Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I'm trying to pass a date variable to a SQL query in postgres but no matter what conversion method I apply SQL sees the date as integer (e.g. 46801).
e.g.
LET vmaxdate = Date(lookup('maxdate', 'tableName', 'facttable' , 'historytable')) ,'D/MM/YYYY') ;
LOAD *;
SQL
SELECT *
FROM acc
WHERE a.acc_date >= $(vmaxdate ); <<<===
postgresql reports error due to comparison between date and integer. I have tried all types of Date/Date#/Text, before WHERE clause but still qlik script passes the variable as integer to the SQL. (If I write this variable to a qvd I can make it show as D/MM/YYYY.).
I also tried cast and to_date in SQL, to no avail. Is there anyway to get around this ?
Thanks.
Try this
LET vmaxdate = Num(lookup('maxdate', 'tableName', 'facttable' , 'historytable')));// ,'D/MM/YYYY') ;
LOAD *;
SQL
SELECT *
FROM acc
WHERE a.acc_date >= $(vmaxdate ); <<<===
Try it with single-quotes around the variable:
...
WHERE a.acc_date >= '$(vmaxdate)';
- Marcus
sorry guys, none of those worked.
After assigning the variable set a trace-statement to look on their result, like:
trace '$(vmaxdate)';
If I look on your lookup-expression I wouldn't be surprised if they don't returned any value respectively your exprected value - maybe the order from the parameters is wrong and/or that all parameters are assigned with single-quotes which is not common.
- Marcus
Trace was useful. I can now generate a query like this in Qlik script (I generate where clause as string first)
LET vtxt1 = 'WHERE acc_date >= ' & Chr(39) & '$(vmaxdate)' & Chr(39) ;
LOAD *;
SQL
SELECT *
FROM ...
$(vtxt1);
which generates:
LOAD *;
SQL
SELECT *
FROM ...
WHERE acc_date >= '9/12/2016';
However it still fails when passed to Progresql complaining about date and integer comparison. I'm not sure why because I can run exact same query in Progresql manually and it works.
This is silly because I never had this problem with SQLserver
Thanks guys, I finally solved this by creating SQL first then passing that to database. It is exactly same query but now Postgres accepts it.
LET vmaxdate = lookup('maxdate', 'tableName', 'factAcc' , 'historytable') ;
LET myquery= 'SELECT * FROM ... WHERE acc_date >= ' & Chr(39) & '$(vmaxdate)' & Chr(39) ;
Trace $(myquery);
LIB CONNECT TO ... ;
factTable:
LOAD *;
SQL $(myquery)
;