DSXchange

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.

Welcome aboard. :D

NULL is always a tricky concept, and therefore should be explicitly handled.
It is also the case that your expression 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.

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.

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

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

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 ...

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

I suggest check from empty after triming

Trim(Column)=''


--Anupam

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

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' 

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.

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.

Perhaps to Dsxchange :D