Qlik Community

QlikView Publisher

Discussion Board for collaboration on QlikView Publisher.

Not applicable

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?

2 Replies
Employee
Employee

Re: SQL common table expression works qvw on desktop, but not in Publisher

Verify the Qlikview Version in this two systens.

Try to run Qlikview Desktop with service account of Qlikview Distribution Services and analyze if this problem still hapening.

The same syntax is used by Qlikview Destkop and Qlikview Publisher.

Ricardo Gerhard
OEM Solution Architect
LATAM

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.

Best,

Peter

Community Browser