IsNull() in transformer...

allavivek · Post by **allavivek** » Thu Sep 30, 2010 5:50 pm

Hi All,,

For one field from input file iam getting null values , length is 0....

i was trying to load in oracle dtabase....all records are getting dropped and sql code is -1400...i know its due to null values issue...

the equation i used in transformer is

if IsNull(DSLink150.xxxx) then '9' else DSLink150.xxxxxx

but i was getting empty values and records are getting dropped resulting in sql code -1400....

Any suggestions...

chulett · Post by **chulett** » Thu Sep 30, 2010 6:06 pm

In Oracle, an empty string is converted to a null... they are equivalent. And an empty string would test 'true' as a null, so test for both.

allavivek · Post by **allavivek** » Thu Sep 30, 2010 6:29 pm

chulett wrote:In Oracle, an empty string is converted to a null... they are equivalent. And an empty string would test 'true' as a null, so test for both. ...

chulett i didnt get you..

so you are telling that in oracle null and empty string (both are having length 0) are same...

thanks...

anbu · Post by **anbu** » Thu Sep 30, 2010 6:38 pm

Chulett was mentioning about testing your input for empty string

Code: Select all

if IsNull(DSLink150.xxxx) And Trim(DSLink150.xxxx) = '' then '9' else DSLink150.xxxxxx

allavivek · Post by **allavivek** » Thu Sep 30, 2010 7:00 pm

anbu wrote:Chulett was mentioning about testing your input for empty string
Code: Select all
if IsNull(DSLink150.xxxx) And Trim(DSLink150.xxxx) = '' then '9' else DSLink150.xxxxxx 

i anbu thanks it worked but placed OR in place of AND...

chulett · Post by **chulett** » Thu Sep 30, 2010 8:16 pm

allavivek wrote:so you are telling that in oracle null and empty string (both are having length 0) are same...

I was saying that Oracle considers them equivalent, converting one to the other - DataStage does not, however.