Skip to Content
avatar image
Former Member

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jan 11, 2017 at 11:50 AM

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

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 11, 2017 at 12:01 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Jan 31, 2017 at 04:59 PM

    Thank you Amit. This helped

    Add comment
    10|10000 characters needed characters exceeded