Qlik SQL Load with Semicolons

[mikedeee]

There’s a lot of static to be read on the forums for those times you need to send a SQL query with semicolons from Qlik.

Here’s a way that should work as we don't always have the ability to save stored procedures.

Create a SQL variable and send that via a Qlik variable.

• The variables will hide the semicolons from Qlik
• You don’t need to use CHAR(59) for the semicolons as some sources suggest
• You will need to replace all SQL single quote query characters , “ ‘ “, with  CHAR(39)
• The SQL variable uses single quotes as the defining element: Red below
• The Qlik variable wraps everything in double quotes: Blue below
• The SQL execution is in the Qlik variable

Example: Qlik Load

//Qlik Variable (Change ‘Set vSql’ to ‘Let vSql’ when needed)

Set vSql ="

DECLARE @SQL VARCHAR(8000)

SET @SQL =

'SET NOCOUNT ON;

declare @starttime as datetime2;

declare @endtime as datetime2;

set @starttime = getdate();

IF OBJECT_ID(' + CHAR(39) + 'tempdb..#tempresults' + CHAR(39) + ') IS NOT NULL DROP TABLE #tempresults;

with cte as (

select AdmitDateTime as LastPatientRegActivity,

RegistrationType,

ROW_NUMBER() OVER (ORDER BY AdmitDateTime desc) as num

from [dbo].[YourTable]

where RegistrationType in (' + CHAR(39) + 'IN' + CHAR(39) + ',' + CHAR(39) + 'INO' + CHAR(39) + ',' + CHAR(39) + 'ER' + CHAR(39) + ')

)

select

datediff(mi,cte.LastPatientRegActivity,getdate()) as MinutesSinceLastPatientActivity,

cte.LastPatientRegActivity as LastPatientRegActivityDateTime

into #tempresults

from cte

where num = 1;

set @endtime = getdate();

select *, datediff(ms,@starttime,@endtime) as LoadTimeMS

from #tempresults;

IF OBJECT_ID(' + CHAR(39) + 'tempdb..#tempresults' + CHAR(39) + ') IS NOT NULL DROP TABLE #tempresults;'

EXEC(@SQL);

";

Sample Load

Temp1:

Load *;

SQL

$(vSql);