Skip to Content
0
Oct 14, 2009 at 02:03 PM

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

206 Views

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.