ifthenelse / decode pushdown issue

dataservices job design
1

The following mapping is causing some concern:

ifthenelse((DARR_SRC.COL1 = ‘0’), null , ‘B’)

If we look at the generated SQL for this mapping it is simply returning the column, not actually performing the ifthenelse logic.

If we tweak the mapping slightly to this:

ifthenelse((DARR_SRC.COL1 = ‘0’), ‘A’ , ‘B’)

then we generate the following SQL

SELECT ( DECODE( “DARR_SRC”.“COL1” , ‘0’, ‘A’, ‘B’) )
FROM “xxx”.“DARR_SRC” “DARR_SRC”

If looks as if the ifthenelse function will no longer be pushed down to the database (as a decode on Oracle) if the true condition resolves to NULL.

This is on 11.7.3.3

We have checked on 6.5 and this is pushed-down to Oracle. It seems like 6.5 seems to handle this much better than 11.7.3.3

Anybody have any idea why this was changed, or can you think of any situations where null was causing problems that would have made BO force this on to the engine?

dcstevenson (BOB member since 2006-09-06)

2

There have been similar issues reported where the first result was 0 (versus NULL in your case) - they were fixed in 11.7.3.10. You might try to see if it works properly in that version, for you too.

dnewton :us: (BOB member since 2004-01-30)

3

My bet is related to data type conversions. What is the datatype returned by the ifthenelse()? The datatype of the THEN part. So in case it is an ‘A’ the datatype is obviously a varchar(1) and that fits into the target column.
In case of a NULL the datatype is not known and the ELSE part might be a varchar(10). A SQL writing 10 chars into a varchar(1) will fail.

So in 6.5 there is some probability a dataflow fails if it is pushed down, in later releases we avoid that. Obviously we still can do better.

Werner Daehn :de: (BOB member since 2004-12-17)