topic Re: No data using date in oracle DB where clause in Connectivity & Data Prep

No data using date in oracle DB where clause

monaali602 — Sun, 06 Aug 2017 14:42:15 GMT

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:MI:SS');

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

zhadrakas — Mon, 07 Aug 2017 06:07:46 GMT

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:MI:SS')) = $(vMaxCrtDate);

regards

tim

monaali602 — Mon, 07 Aug 2017 07:12:19 GMT

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 — Mon, 07 Aug 2017 08:36:16 GMT

SQL:

SELECT a.fld1,a.fld2

FROM tablename a

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