topic Concatenate SQL string with variables and special characters in QlikView

Concatenate SQL string with variables and special characters

Mon, 30 Jan 2017 13:31:47 GMT

Hi everyone,

I am want to feed the following query with content via variables in the load script:

SELECT

$(vSQL_Columns)

FROM

$(vDatabase)$(vTableName)

$(vJoinStatement)

WHERE

$(vWhereClause_fin)

$(vIncrementalDate) < '$(vPrevMthStart)';

This is all straight forward until I want to parse the $(vWhereClause). The where-clause obviously contains special characters like <, > and =.

vWhereClause_fin is populated and assembled as follows. It should translate into "T1.Column1 < 14 and T1. Column1 > 15 AND".

LET vWhereClause	=	"T1.Column1 " & chr(60) & " 14 AND T1.Column1 " &chr(62) & " 15";
LET vWhereClause_fin	=	If($(vWhereClause)="","",$(vWhereClause) & " AND ");
LET vWhereClause_fin	=	If($(vDebug)=1,$(vWhereClause_fin) & $(vDebugWhereClause) & " AND ",$(vWhereClause_fin));

As soon as I try that, I get a script error in return. What am I missing?

Thanks for your time and help!

Henning

vinieme12 — Mon, 30 Jan 2017 13:50:53 GMT

Use single quotes when referring to string variable

LET vWhereClause	=	"T1.Column1 " & chr(60) & " 14 AND T1.Column1 " &chr(62) & " 15";
LET vWhereClause_fin	=	If($(vWhereClause)="","",'$(vWhereClause)' & " AND ");
LET vWhereClause_fin	=	If($(vDebug)=1,'$(vWhereClause_fin)' & '$(vDebugWhereClause)' & " AND ",'$(vWhereClause_fin)');

Mon, 30 Jan 2017 13:52:56 GMT

Lovely, thank you!