I have this SQL that I’d like to convert to a BAQ. Do you know How I would set this up? I need a CTE to generate a specified row of numbers. Ultimately using this BAQ to feed a BAQ report to generate some labels.
declare @begNumber as int = 1
declare @endNumber as int = 50
--print 1 to 50 numbers
; with CTE as
(select @begNumber Number
union all
select Number +1 from CTE where Number < @endNumber
)
select * from CTE
First, make a subquery that’s a CTE. Then a calculated field with your beginning number. (or a parameter if you need to to be different)
Then make a union all.
Add the CTE and put in the criteria for your end number.
Create a calculated field to take the CTE +1
Then make a top level with the CTE in it.
Add the display field.
$$Profit$$
Thank you!
@Banderson I’m so jealous of your CTE skills. Obviously this example is pretty straight forward. But I struggle with them EVERY DAMNED TIME!!
I always search for your posts when I’m working on one, haha.
This works if your iteration is less than 100. On SaaS, that’s set in stone. If you’re self hosted, you could change the value of MAXRECURSION, but keep in mind it’s like that because of reasons, and you have access to a better solution anyway.
Recursion limitation isn’t the end of the world. It’s easy to CTE your way to arbitrary sequences!
with foo as(
select
null as whatever
union all
select
null as whatever
), tally as)
select
row_number() over(order by foo.whatever) as increment
from foo --< returns two rows
join foo as a on 1=1 --< cross joins two rows: 2 * 2 = 4 rows
join foo as b on 1=1 --< 8 rows
join foo as c on 1=1 --< 16 rows
join foo as d on 1=1 --< 32 rows
-- see where this is going?
)
That’s how you get to arbitrary iteration in SQL, without recursion. It’s as performant as you’re going to get on SaaS. If you’re self hosted, this is how you can populate a tally table (literally, create table tally(increment int primary key);) that SQL Server will cache in memory for best possible performance.
It’s not. I have a generator I made, I’ll see if I can find it.
I didn’t cheat either.
If you are doing recursion that is more than 100 levels deep you are probably using the wrong application or doing something incorrectly.
Recursion is very expensive (in all/most languages) as it requires full stack chain replication. Every iteration adds a new reference frame to the call stack and would eventually overflow you. It is a powerful tool, but Spiderman Rules apply.
Wait, why not just use ROW_NUMBER instead of creating a CTE?
You can use Row_Number but you need to generate 100 rows (that’s what teh CTE is for)
I never asked “Why?”… Someone just asked how, and I answered.
I looked everywhere, it’s gone.
I have no need for it, so not figuring it out again.
All I hear Is you don’t know how to do it… 
#ProveMeWrong
^ this is how you get him to do things … it works on me at least 
Remind me week after next.
Vacation is coming next week, and they are beating me with requests.
Case in point. It matters almost none, and he took to time to open a ticket.
I’m still gunning for he most PRBs opened in a year award!
There’s no award…
