topic Construct a string with single quotes in it in QlikView

Construct a string with single quotes in it

amit_shetty78 — Sat, 15 Aug 2009 00:02:34 GMT

Hi All,

I am trying to create a sql statement dynamically from a variable:

LET Condition = 'WHERE TimeStart >=' & date(fieldValue('maxdate', 1),'YYYY-MM-DD hh:mm:ss.sss');
I am further using this variable in a SQL query:
LOAD a, b, c;
select a, b, c from ExecTbl
$(Condition)
;

This is giving an error because the generated SQL statement is:
select a, b, c from ExecTbl WHERE TimeStart >= 2009-08-09 01:02:53.403;

I would like it to be:
select a, b, c from ExecTbl WHERE TimeStart >= '2009-08-09 01:02:53.403';

Pls advice.

Thnks, Amit.

Construct a string with single quotes in it

Sat, 15 Aug 2009 00:53:14 GMT

Declare another variable,

vSign = '

and then flank your date value with the variable??


 LET Condition = 'WHERE TimeStart >=' vSign& date(fieldValue('maxdate', 1),'YYYY-MM-DD hh:mm:ss.sss')&vSign;

Construct a string with single quotes in it

hector — Sat, 15 Aug 2009 05:26:19 GMT

Hi, you can concatenate the function Chr() with 39 as parameter (Chr(39)), this function gives you the ' char

or in a complex case use the replace like this

Let strSQL = ' where company in (#C1#,#C2#)';

Let strSQLv2 = replace(strSQL,'#',Chr(39));

and the result is

strSQLv2 = where company in ('C1','C2')

Regards

Construct a string with single quotes in it

amit_shetty78 — Mon, 17 Aug 2009 17:02:27 GMT

Thanks Chai and Hector. Suggestions provided by both of you are very useful.

Cheers, Amit.