Essentially if an account has not been terminated a date way in the future is substituted in and Y will be returned.
However in DS 3.2 this causes Oracle to error with the following: ORA-01847: day of month must be between 1 and last day of month. If I play around with the format of the date in Data Services the job fails to validate so I appear to be stuck in a position where DS thinks the SQL is valid and Oracle does not.
During the upgrade I’ve also swapped the Oracle client on the job server from 9i to 10g but not sure whether this will have had an impact.
It is a different pushdown, the ifthenelse(nvl() is now pushed down to Oracle and not executed inside the engine as before.
Basically you have a conversion problem:
ifthenelse((TRANSACTIONS.DATE <= nvl(Query.ACCOUNT_TERMINATED_DATE, ‘9000.12.01 00:00:00’)), ‘Y’, ‘N’)
I assume the two …DATE columns are of type datetime and not varchar, hence the second parameter of the nvl() function has to be converted to a datetime as well. But using what format?
The string you specified to be converted is in the default format of DI. So if that conversion happens in DI, you would be okay. But as we can pushdown this kind of ifthenelse() function with a less-than condition, we push down the nvl as well, and now Oracle does the string-to-date conversion using its default. And that does not seem to be the same.
Either way, the version without an implication conversion is:
It is a different pushdown, the ifthenelse(nvl() is now pushed down to Oracle and not executed inside the engine as before.
One of the Best Practices that I preach to my clients is to never, ever use an implicit conversion of a string to a date data type and vice versa. It may work in dev, it may work in QA, it may even work in production. But for how long? When will something change such that the assumed date format is different?
If you had used an explicit conversion from string to date (using to_date() like Werner suggested above) then this issue would never have come up. It would not surprise me to find that you have other date conversion issues that are ticking like a time bomb waiting to go off.
The default date format can be different between machines. This can be set within the Oracle client but it can also be set on the Oracle server I believe. So your dev job server may use one format and the production job server could use a different format.
Also, try to be consistent in the format used. I followed behind another consultant that used DD-MM-YYYY while everyone else used MM-DD-YYYY. This non-standard format caused a couple problems within jobs but mostly it caused confusion when looking at dates printed to the trace logs.
(BOB member since 2007-08-17)
(BOB member since 2004-12-17)
(BOB member since 2007-09-12)