Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Oracle SQL Where Clause using date variable for filter

Hi Experts,

I have following script to fetch and load data into incremental qvd and failing at date conversion.

LIB CONNECT TO 'oradb';

Set vQVDPath = QVDs/;

Set vThisExecTime=Now();

Let vLastExecTime = Timestamp(If(IsNull(QvdCreateTime('$(vQVDPath)EMP_ALLOC.QVD')), 0, ConvertToLocalTime(QvdCreateTime('$(vQVDPath)EMP_ALLOC.QVD'), 'GMT', 1)), 'YYYY-MM-DD hh:mm:ss');

EMP_ALLOC:

SELECT EMP_ID,SHIFT_DT,SHIFT_NO,SUPERVISOR_ID,LAST_UPDT

FROM EMP_ALLOC

WHERE LAST_UPDT>='$(vLastExecTime)'

AND LAST_UPDT<'$(vThisExecTime)';

1 Reply
Ezir
Creator II
Creator II

Sugiro fazer uma formatação da variável e do campo de busca para o mesmo formato de data conforme exemplo abaixo.

A variável ficaria assim:

Let vThisExecTime=Timestamp(Now(),'YYYY-MM-DD hh:mm:ss'); 

Let vLastExecTime = Timestamp(If(IsNull(QvdCreateTime('$(vQVDPath)EMP_ALLOC.QVD')), 0, ConvertToLocalTime(QvdCreateTime('$(vQVDPath)EMP_ALLOC.QVD'), 'GMT', 1)), 'YYYY-MM-DD hh:mm:ss');

E a claúsula WHERE ficaria assim:

WHERE to_char(LAST_UPDT,'YYYY-MM-DD hh24:mm:ss')>='$(vLastExecTime)'
AND to_char(LAST_UPDT,'YYYY-MM-DD hh24:mm:ss')<'$(vThisExecTime)';