Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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)';