Problems with Where Predicate in AS400 ODBC

[#1]

Hi all,

I am working about an incremental load, the first test works fine when i use:

SQL SELECT * FROM TableName where timestamp(H2DBT3) >= '2011-03-07 13:28:41.76';

But i need include this predicate in a variable like:

LET INCREMENTAL_EXP = 'WHERE timestamp(H2DBT3) >=' & fieldValue('maxdate', 1);

SQL SELECT * FROM TableName $(INCREMENTAL_EXP);

Don't work, ODBC ERROR

Then i test this:

LET INCREMENTAL_EXP = " WHERE timestamp(H2DBT3) >= '2011-03-07 13:28:41.76'";

SQL SELECT * FROM TableName $(INCREMENTAL_EXP);

Don't Work, i have not errors but load all the rows, 'where' is ignored...

I use QV SR5 64Bit and HIT ODBC Server 400 64 Bit.

Can you help me?

Thanks in advance,

JA

[rwunderlich · Accepted solution]

If you need to quote the value, code the LET like this:

LET INCREMENTAL_EXP = 'WHERE timestamp(H2DBT3) >= ' & chr(39) & fieldvalue('maxdate', 1) & chr(39);

Look at the document log file to confirm what is being passed in the SQL statement is what you expect.

[#3]

Perfect and Fast Solution; Rob,

Thank you very much for your help and congratulations for http://qlikviewnotes.blogspot.com

KR,

JA