Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
narender123
Specialist
Specialist

Use timestamp with incremental load of oracle data base field

Hi team,

I am doing incremental load on timestamp oracle database field.

oracle database fieldoracle 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.

Screenshot_3.jpg 

Please guide me to sort out this error.

Thanks,

Narender

 

 

 

 

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

7 Replies
marcus_sommer

Try it with quotes around your variable like:

sql select * from awprod.un_asyman_track where Op_date_time > '$(var1)';

- Marcus

 

narender123
Specialist
Specialist
Author

Hi Marcus,

 

It is not working with quotes.

Screenshot_4.jpg

Thanks,

marcus_sommer

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

narender123
Specialist
Specialist
Author

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

 

narender123
Specialist
Specialist
Author

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

marcus_sommer

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

narender123
Specialist
Specialist
Author

Thanks Marcus.

 

I will check it and post on the same.

 

-Narender