SQL common table expression works qvw on desktop, but not in Publisher
I have a qvw that uses a common table expression in sql. The common table expression begins with "with cte as (select x,y,z from table) "
The script executes on my desktop but fails when running it on Publisher. The log file shows a SQL error, saying the "with statement is required to be preceded by a semicolon.
I know of a number of ways to re write the sql to avoid the cte. I also could store the sql as a variable or even call it as a stored procedure. My question is -- why does it work on my desktop but fail on Publisher? I've checked the SQL drivers on the publisher server and they seem fine, the same as are installed on my desktop. Any ideas?
Re: SQL common table expression works qvw on desktop, but not in Publisher
Is there some conditional code immediately before the WITH CTE AS... part that expands to something wrong (like a SQL statement without semicolon) on the QlikView server (but does not in your Desktop)? Although a semicolon isn't strictly required in T-SQL up until SQL Server 2014, some SQL statements don't like to be glued together as that may lead to ambiguous code. Just an idea.
The best thing you can do is post the offending part of your script. Please do add the part before the WITH CTE AS line.