Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi team,
I am doing incremental load on timestamp oracle database field.
Now I have to apply incremental load on Op_date_time field in qlikview. For this i need to insert max value in variable and apply grater than (>) condition in that variable in where clause as per steps in incremental load.
lastupdateddate:
load timestamp(max( Op_date_time,'DD-MM-YYYY hh:mm:ss.ffffff')) as max_op_date_time
resident table1;
let var1= peek('max_op_date_time ',0,'lastupdateddate');
noconcatenate
load *;
sql select * from awprod.un_asyman_track where Op_date_time>$(var1);
It is showing error in last sql statement.
Please guide me to sort out this error.
Thanks,
Narender
Depending on your default number-formatting you might not need any formatting because max() will return the value within your default format whereby because of the fact that the value should be later called from a variable the value mustn't contain any comma because it would be treated as parameter-delimiter.
I don't know which functions are possible/valid in your oracle but the following should be a good starting point: in-oracle-covert-timestamp-to-number.
- Marcus
Try it with quotes around your variable like:
sql select * from awprod.un_asyman_track where Op_date_time > '$(var1)';
- Marcus
Hi Marcus,
It is not working with quotes.
Thanks,
Maybe your created timestamp is invalid from a database point of view. Your view in Oracle might be formatted in some way and not displaying how the data are really stored. Another cause could come from your driver which might not be able to handle it properly. Before playing with the formatting-stuff I would try to transfer the condition as a pure number with a num() instead of a timestamp() on the Qlik side and applying the same with Oracle functions on the Oracle side.
- Marcus
Thanks for your suggestion Marcus.
Should I use num() like
num(timestamp(max( Op_date_time,'DD-MM-YYYY hh:mm:ss.ffffff')))
Right?
But I dont know how to do the same in oracle under Sql select statement.
-Narender
oracle database time format is
NLS_TIMESTAMP_FORMAT = DD-MON-RR HH.MI.SSXFF AM
In table script the field date type is timestamp(6).
-Narender
Depending on your default number-formatting you might not need any formatting because max() will return the value within your default format whereby because of the fact that the value should be later called from a variable the value mustn't contain any comma because it would be treated as parameter-delimiter.
I don't know which functions are possible/valid in your oracle but the following should be a good starting point: in-oracle-covert-timestamp-to-number.
- Marcus
Thanks Marcus.
I will check it and post on the same.
-Narender