Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
Hi,
U Try Chr(34)&Command &Chr(34) .I thing used for ur Problem
Regards
Perumal A
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!
Anyone... Beuhler?
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);
Thanks! This saved my day!