topic How to utilize a SQL query that includes a semicolon? in QlikView

How to utilize a SQL query that includes a semicolon?

Thu, 09 Dec 2010 00:06:16 GMT

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!

How to utilize a SQL query that includes a semicolon?

Tue, 13 Dec 2011 18:52:03 GMT

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.

How to utilize a SQL query that includes a semicolon?

perumal_41 — Wed, 14 Dec 2011 05:09:23 GMT

Hi,

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

Regards

Perumal A

How to utilize a SQL query that includes a semicolon?

Thu, 15 Dec 2011 14:42:41 GMT

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!

How to utilize a SQL query that includes a semicolon?

Thu, 12 Jan 2012 15:00:59 GMT

Anyone... Beuhler?

Re: How to utilize a SQL query that includes a semicolon?

Mon, 29 Oct 2012 18:59:36 GMT

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);

Re: How to utilize a SQL query that includes a semicolon?

erikkorme — Fri, 25 Jul 2014 10:51:29 GMT

Thanks! This saved my day!