In Informix DataBase condtion for NULL value getting fail

prasoon · Post by **prasoon** » Sun Apr 16, 2006 11:33 pm

Hi
In DS I given following condition

If (Column1= 'Y' AND Column2= 'Y' AND Column3= 'Y' AND Column4= 'Y' then 'Y' else 'N')

But when ALL the column's getting NULL value then in Target Table updating with NULL value. According to my requirement It should be either 'Y'(if Condition thru) or 'N'

Due to this problem most of the record not updating in target Table!!

Please suggest me How I can solve this problem.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 17, 2006 12:52 am

Welcome aboard. :D

NULL is always a tricky concept, and therefore should be explicitly handled.

Code: Select all

If (IsNull(Column1) Or IsNull(Column2) Or IsNull(Column3) Or IsNull(Column4)) Then 'N' Else If (Column1= 'Y' AND Column2= 'Y' And Column3= 'Y' AND Column4= 'Y') then 'Y' else 'N'

It is also the case that your expression

Code: Select all

If (Column1= 'Y' AND Column2= 'Y' AND Column3= 'Y' AND Column4= 'Y' then 'Y' else 'N')

has the right parenthesis misplaced - it should follow "Column4='Y'". This alone may be a sufficient correction, since NULL always takes the ELSE path in an IF statement, on the grounds that it can never be asserted to be "true".

However, I always advocate handling NULL explicitly, if for no other reason than to communicate to the next developer that I've considered it.

prasoon · Post by **prasoon** » Mon Apr 17, 2006 3:12 am

In Data Stage Transformation following condition given

IF Trim (gender) = 'Male' OR Trim (gender) = 'Female' OR Trim (gender) = 'Indeterminate' THEN 'Y' ELSE 'N'

Source: Table 1
Column Nullable
Gender No

Target: Table 2
Column Nullable
Sex Yes

Please let me know how some of the row of Table 2 getting null values.

prasoon wrote:Hi
In DS I given following condition

If (Column1= 'Y' AND Column2= 'Y' AND Column3= 'Y' AND Column4= 'Y' then 'Y' else 'N')

But when ALL the column's getting NULL value then in Target Table updating with NULL value. According to my requirement It should be either 'Y'(if Condition thru) or 'N'

Due to this problem most of the record not updating in target Table!!

Please suggest me How I can solve this problem.

diamondabhi · Post by **diamondabhi** » Mon Apr 17, 2006 12:20 pm

U need to handle nulls and sometimes u need to even check for spaces,
ex: len(trim(linkname," ","A"))=0

ray.wurlod · Post by **ray.wurlod** » Mon Apr 17, 2006 3:08 pm

Where is the source coming from? Just because you declare a column to be not null in your DataStage job does not enforce non-nullability. For example, if your source is a text file, the Format tab on the Sequential File stage specifies what will be interpreted to be null (by default it is "") and this is irrespective of the nullability setting for that column. You can override the definition of null representation for particular columns in the Columns grid. Scroll to the right to find it. Replace the empty string with something else, such as "<NULL>".

Post number 11111

prasoon · Post by **prasoon** » Tue Apr 18, 2006 2:42 am

Source is also INFORMIX

ray.wurlod wrote:Where is the source coming from? Just because you declare a column to be not null in your DataStage job does not enforce non-nullability. For example, if your source is a text file, the Format tab o ...

gateleys · Post by **gateleys** » Tue Apr 18, 2006 6:53 am

ray.wurlod wrote:Post number 11111

Great going Ray. The numbers come with a wealth of information for all of us here in the forum. Thanks.

gateleys

sb_akarmarkar · Post by **sb_akarmarkar** » Tue Apr 18, 2006 7:04 am

I suggest check from empty after triming

Trim(Column)=''

--Anupam

kumar_s · Post by **kumar_s** » Tue Apr 18, 2006 8:26 am

What is the target column datatype?
What if some of the input fields comes as expetec (is it getting populated with either 'Y' or 'N')?
What if you try

Code: Select all

IF (Trim(gender) = "Male" OR Trim(gender) = "Female" OR Trim(gender) = "Indeterminate") THEN 'Y' ELSE 'N'

prasoon · Post by **prasoon** » Mon May 08, 2006 5:23 am

1.Target column datatype is CHAR
2.other column are populatting according to condition.
3.I'm using following condition
IF (Trim(gender) = "Male" OR Trim(gender) = "Female" OR Trim(gender) = "Indeterminate") THEN 'Y' ELSE 'N'

kumar_s wrote:What is the target column datatype?
What if some of the input fields comes as expetec (is it getting populated with either 'Y' or 'N')?
What if you try
Code: Select all
IF (Trim(gender) = "Male" OR Trim(gender) = "Female" OR Trim(gender) = "Indeterminate") THEN 'Y' ELSE 'N' 

kumar_s · Post by **kumar_s** » Mon May 08, 2006 5:47 am

Ooff... a quick response...
Are you sure you are lookin after the output directly after the transformation and not after any join or merge where the null will play a vital role.
Try using debugger to know rows wise processing.

prasoon · Post by **prasoon** » Mon May 08, 2006 6:14 am

Sorry Kumar for late response....
Ok Now I got the reason why data is not updating those record.
In that job near about 12 Transformation using I was concentrated only on derivation given on Transformation.

But Problem is from source table only data is not fetching for those 268 record among 23458690 record.
I have to look on that only!!

Thanks a lot for support kumar!!

kumar_s wrote:Ooff... a quick response...
Are you sure you are lookin after the output directly after the transformation and not after any join or merge where the null will play a vital role.
Try using debugger to know rows wise processing.

kumar_s · Post by **kumar_s** » Mon May 08, 2006 6:28 am

Perhaps to Dsxchange :D

DSXchange

In Informix DataBase condtion for NULL value getting fail

In Informix DataBase condtion for NULL value getting fail

Re: In Informix DataBase condtion for NULL value getting fai