Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

monaali602
New Contributor II

No data using date in oracle DB where clause

Hi all,

I am using ODBC Connection to Oracle DB and need to apply incremental load using date

I have written the following code

MaxDateTable:

LOAD Max(CREATION_DATE) AS MaxDate FROM [filename.qvd](qvd);

LET vMaxCrtDate = Date(Peek('MaxDate',0,'MaxDateTable'),'DD/MM/YYYY hh:mm:ss');

[Updatedtable]:

LOAD

fld1,

fld2;

   

SQL

SELECT fld1,fld2

FROM tablename

where CREATION_DATE = TO_DATE('$(vMaxCrtDate)', 'DD/MM/YYYY HH24:MISmiley FrustratedS');


The equals operator in where clause does not retrieve any data

in addition when replace where clause with

TRUNC(CREATION_DATE) = '29/04/2011'

no data retrieved despite same query run on TOAD and 43 records retrieved.

any help? thanks in advance


3 Replies
zhadrakas
Valued Contributor

Re: No data using date in oracle DB where clause

you could try to compare date as numbers:

QV:

LET vMaxCrtDate = num(Peek('MaxDate',0,'MaxDateTable'));

Oracle:

where  ceil (sysdate - to_date(CREATION_DATE, 'DD/MM/YYYY HH24:MISmiley FrustratedS')) = $(vMaxCrtDate);

regards

tim

monaali602
New Contributor II

Re: No data using date in oracle DB where clause

Thanks for help

it doesn't work also

but when i used floor(creation_date) = '29/04/2011' in load where statement instead of SQL it retrieved data correctly,

the problem is in SQL is there special interpretation for date in ODBC i tried many formats.

sth_wch004
New Contributor III

Re: No data using date in oracle DB where clause

SQL:

SELECT a.fld1,a.fld2

FROM tablename a

where TRUNC(a.CREATION_DATE) = (SELECT TRUNC(MAX(b.CREATION_DATE)) FROM  tablename b)

Community Browser