Passing date variables to Postgres fails

[uzername]

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.

[shubham_singh]

Try this

LET vmaxdate = Num(lookup('maxdate', 'tableName', 'facttable' , 'historytable')));// ,'D/MM/YYYY')  ;

LOAD *;

SQL

SELECT *

FROM acc

WHERE a.acc_date >=  $(vmaxdate );  <<<===

[marcus_sommer]

Try it with single-quotes around the variable:

...

WHERE a.acc_date >=  '$(vmaxdate)';

- Marcus

[uzername]

sorry guys, none of those worked.

[marcus_sommer]

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

[uzername]

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

[uzername]

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)

;