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.
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.
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.