topic Re: Oracle SQL Where Clause using date variable for filter in QlikView

Oracle SQL Where Clause using date variable for filter

Thu, 02 Mar 2017 06:37:26 GMT

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)';

Re: Oracle SQL Where Clause using date variable for filter

Ezirraffner — Mon, 13 May 2019 18:50:46 GMT

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)';