How to utilize a SQL query that includes a semicolon?

[#1]

I want to use an existing SQL query that uses Common Table Expressions in QlikView. The CTE requires that the preceeding SQL code be terminated with a semicolon before the "WITH" command. Unfortunately, QlikView interprets this semicolon as the end of the SQL statement. Does anyone know a workaround for this? Thanks!

[#2]

I am considering the same problem but I have a lot of code prior to the CTE.  Maybe set the SQL into a variable utilizing CHR() if need be.

[perumal_41]

Hi,

U Try Chr(34)&Command &Chr(34) .I thing used for ur Problem

Regards

Perumal A

[#4]

Thanks for your response, Permual. I think you might have the solution, but I'm not  sure I undserstand exactly. Could I ask you to modify the code snippet below to show me exactly how you think your solution should work?

Here's the code:

Set datefirst 1;

With MyCTE as

     (Select ordno

               , invno

               , total

     from orders..ordertable (nolock)

     )

...

Thanks!

[#5]

Anyone... Beuhler?

[#6]

I know this may be a little late, but I found your question when I ran into a similar issue.  Here is the result I used:

In Qlikview Script ...

set vSQL = "DECLARE @SQL VARCHAR(500)

SET @SQL = '

USE [BI-Transform]' + CHAR(59) + '

WITH myCTE AS (

SELECT TOP 10 * FROM dbo.tblVariables)

SELECT * FROM mycte' + CHAR(59)

EXEC(@SQL)";

Demo:

Load *;

sql $(vSQL);

[erikkorme]

Thanks! This saved my day!