can I use CTE tables in IDT Universe . I am using BO 4.1, service pack 6.

[Former Member]

can I use CTE tables in IDT Universe. I am using BO 4.1, service pack 6.

Below is the expression I am using:

WITH CTETableName AS ( SELECT 'AB' as Code, 'AppleBannana' as Description UNION SELECT 'CD' as Code, 'CarsDoddle' as Description UNION and so on) SELECT * FROM TableName

The reason why I am using this CTE table is there is no fct or dimenson table in datawarehouse nor there is a lookup table. This is a temporary solution till the lookup table is created in Datawarehouse.

[Former Member]

Thank you Amit. This helped

[Former Member]

Hi Amit,

Thank you for your response. I created a derived table using the baove SQl expression, but I am getting the error message saying:

Cause of Error Incorrect syntax near the keyword 'WITH'.

The trouble is when I ran this query in Datawarehouse it is giving me the dataset. Not working in IDT when I right click on the table and click on Table Values.

[amitrathi239]

don't put WITH in the derived table.

Place your SQL statement only and try

SELECT 'AB' as Code, 'AppleBannana' as Description UNION SELECT 'CD' as Code, 'CarsDoddle' as Description UNION and so on) SELECT * FROM TableName

[amitrathi239]

You can try with creating derived table in the IDT universe.

http://www.forumtopics.com/busobj/viewtopic.php?p=745726&sid=6d3c8c65b4f81563c3206da38c9d4e9f