Data Integrator "where" in query transform vs SAP table column date

[cheriehowes]

I have the following problem:

I am using a query transform to grab data from a SAP table and have a "where" that looks like this that works:

ZMM_STOMOS_HIST.ZPOST_DATE = to_date(to_char(sysdate(), 'YYYYMMDD'), 'YYYYMMDD')

BUT it runs for a very long time and I have just realised that when I look at the sql statement via display optimized sql that the "where" statement does not show - which makes sense because it seems to return the entire table rows to DI before it starts to filter them on the "where".

this is even though the pure SAP date is stored as YYYYMMDD type date and in DI we say take it to our BO table as type date u2013 the sql server database made it datetime. And when I validate the sql transform I get this warning.

[Query:Query_1]

BODI-1110411: Conversion warning <Warning: Expression <to_date(to_char(sysdate(), 'YYYYMMDD'), 'YYYYMMDD')> of type <DATETIME> will be converted to type <DATE>.> for expression <ZMM_STOMOS_HIST.ZPOST_DATE = to_date(to_char( sysdate( ),'YYYYMMDD'),'YYYYMMDD')

>.

I can get other "where" conditions to show in the optimized sql on the same table - no problem.

e.g.

SELECT ZSERIAL , ZUMOVETYP , ZUORIGIN , ZUDESTN , ZUTRDATT , ZUTRTIMM , ZUVENDER , ZUBOXSA1 , ZUBOXSA2

FROM ZMM_STOMOS_HIST

WHERE ZUMOVETYP = 'RBX'

How do I get this particular where clause to appear in the optimized sql statement ? I suspect it has something to do with the formatting and have tried many formats but no go.

[Former Member]

try using a varaible instead of to_date and to_char function and use that in the where clause

ZMM_STOMOS_HIST.ZPOST_DATE =$G_CUR_DT

you can initialize $G_CUR_DT in a script as below

$G_CUR_DT=sysdate();

first try with $G_CUR_DT as date datatype if that doesn't work try with varchar datatype

[cheriehowes]

Thank you so much .... I had to use a variable / paramter of type varchar(8) and the value as YYYYMMDD

e.g. my script

$today = to_char(sysdate(),'YYYYMMDD');

print('date is : [$today]');

This has resulted in a huge resource and most of all time saving.

I still dont understand why DI cant just figure this out in a normal where like it does with other datatypes but right now it works so I am happy.