Derived Table Using CTE

building semantic layer
1

Does anyone know whether or not CTE (Common Table Expression) can be used in derived tables in a BO XIR.3 universe? Thanks

shemason1 (BOB member since 2006-05-23)

2

Welcome to B:bob:B!

Thats like a temporary table, right? In which case I’d say no…but that isn’t to say you may not be able to rewrite the sql to achieve the same end without a CTE. Good Luck :wink:

Nick Daniels :uk: (BOB member since 2002-08-15)

3

Thanks Nick. Yes, I ended up rewriting the sql of the derived table. CTE was introduced in sql server 2005.

shemason1 (BOB member since 2006-05-23)

4

Hi,
I have also faced same situation to write a CTE in derived tables it. Designer will not support CTE

krvin :india: (BOB member since 2010-08-16)

5

I did not get it working in IDT, solved it by creating a view in SQL server like:

create view vDate
as
WITH mycte (DateValue) AS
(
select cast(‘1990-01-01’ as datetime) DateValue
union all
select DateValue + 1
from mycte
where DateValue + 1 < ‘2020-12-31’
)
select DateValue
from mycte

select * from vDate
option (maxrecursion 0)

nicothoen :netherlands: (BOB member since 2008-11-20)