SQL WITH statement error

[ashaik1]

Hello - The sql having a WITH statement is giving an error while it i am able to run the same in PLSQL with no issues. The error message says " Error opening "SQL" and ends with No columns returned. Error screen attached. Can you help.

Thanks,

Azhar

[ChrisTX]

Please post the SQL.  The screenshots do not provide enough information to diagnose the problem.

[apathetichell]

If you are using IN-DB this won't work. In-DB does not support CTE (ie WITH)

[LindonB]

Yeah, there's not enough info to give you some syntax changes obviously. Happy to help if you can post the query (or a version of it), but we all know how difficult it can be to adjust a query to be sharable. In the meantime, here's some general info.

“No columns returned” seems to mostly occur either because you filtered out all rows or because the system is unable to retrieve metadata about the columns that a SQL command or stored procedure is supposed to return. Without this metadata, SQL Server Integration Services (SSIS) cannot map columns. I generally see this more when executing stored procedures.

The first thing to try is to check for aliases and metadata; particularly, you want to make sure that aggregations, such and SUM(), have aliases. After that, you can start to try things like SET FMTONLY OFF (which requests column info from the SQL server without running the query).

[Jason_]

I had a similar issue today. After reading this community post I tried SQL with the following syntax, and it worked for me.

Original statement that was not working:
   WITH x AS ([SELECT CLAUSE]),
   y AS ([SELECT CLAUSE])
   SELECT <fields> FROM x INNER JOIN y on x.PK = y.FK

New statement that resolved the issue:
SELECT * FROM

(

   WITH x AS ([SELECT CLAUSE]),
   y AS ([SELECT CLAUSE])
   SELECT <fields> FROM x INNER JOIN y on x.PK = y.FK

)