BusinessObjects Board

IDT SQL server CTE Hierarchy WITH clause

Hi,

I am trying to use a recursive query in a derived table in a UNX universe.
It work excellent when I’m connected to Oracle DB and use th “connect by prior” statment
But I need it also on SQL Server, and there you need to use WITH CTE statement.
The statement parse when validating in IDT (in the edit derived table window) - but when trying to run the query - I get an error msg complaining about:
Incorrect syntax near ‘)’

I cannot create a DB view due to a licensing issue.

Did anyone manage to get this to work?

1 Like

Have you tried copying the SQL statement and running it directly in SQL Server? If it still fails with the same error, you may just have a syntax problem, regardless of whether IDT validates it or not.

1 Like

A CTE has to be the first thing in a SQL Server query - as such, you’ll probably get this to play nicer by moving it to be a subquery within the derived table.

Does IDT support CTEs now? I know that I could not get it work in the past.

Not directly. Either do it via a view or use a subquery/inline view.