passing one column value to another if it is null

Sirisha.lingisetty · Post by **Sirisha.lingisetty** » Fri Apr 21, 2006 12:05 am

Hi everybody.

Please help me to get out of this problem.

I have 3 address fileds in my file. In that , what I need to do is , if the value of address1 is null then address2 value need to be moved to address1 and accordingly address3 value need to be moved to address2.

This should be done for all the 3 fields.

My problem is If I create the unix file with empty value in one field, Datastage is taking that also as value.

For this , while Iam using IsNull() on the field it's assuming that there is a value,and it is not doing anything with my logic.

Thanks in Advance..

sanjeev_sithara · Post by **sanjeev_sithara** » Fri Apr 21, 2006 12:12 am

Hi,

Try doing a Isnull(Trim(field)) and then apply your logic .

HTH

Sirisha.lingisetty · Post by **Sirisha.lingisetty** » Fri Apr 21, 2006 12:26 am

Hi sanjeev,

I tried with your logic, but it's throwing one compilation error.

It seems that all the logic seems to be fine for me.

I wrote this line of code over there.

If IsNull(Trim(DSLink14.address2)) Then DSLink14.address3 Else DSLink14.address2

any comments/Suggestions appreciated.

kumar_s · Post by **kumar_s** » Fri Apr 21, 2006 12:29 am

What is the data type for the field you specified?

Sirisha.lingisetty · Post by **Sirisha.lingisetty** » Fri Apr 21, 2006 12:34 am

Hi,

The datatype for the field is varchar.

I wrote the below line of code.

If IsNull(Trim(DSLink14.address2," ")) Then DSLink14.address3 Else DSLink14.address2

ray.wurlod · Post by **ray.wurlod** » Fri Apr 21, 2006 12:38 am

"" is not the same as NULL. This may be the root cause of your confusion. "" is a known value, NULL represents unknown value. So, if the value is "", then it is not null, and your logic behaves as you have described.

If the data are coming from a file, rather than from a table, there will be a stage property in which you can specify how null is represented in the file. If you get this right, then when that representation is found, the stage will automatically translate whatever you have specified into NULL for downstream processing.

Sirisha.lingisetty · Post by **Sirisha.lingisetty** » Fri Apr 21, 2006 12:45 am

Hi Ray,

Anyway the null handling with trim function is throwing one compilation error.

Please correct if my idea goes wrong.

I think, it can be done setNull() function.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 21, 2006 12:49 am

SetNull() will only work if the target column is defined as Nullable. But SetNull() does not help in deciding whether the input column is null. SetNull() generates a representation of NULL (unknown value).

Sirisha.lingisetty · Post by **Sirisha.lingisetty** » Fri Apr 21, 2006 12:58 am

Hi,

Any of my tries are not working.

Please help me how to check whether it is null or not for a string field.

I tried out with IsNull(),SrcInput field="",trim and all.

Please reply me ASAP

kumar_s · Post by **kumar_s** » Fri Apr 21, 2006 1:06 am

What is the error you get?
And what is the value avaialble in the input file?

ray.wurlod · Post by **ray.wurlod** » Fri Apr 21, 2006 3:41 am

What do you see when you View Data on the Sequential File stage? Are there any NULLs being delivered?

Etiquette note: we are an all volunteer site, we reply as and when we can. Requesting ASAP causes offence. If you want urgent support, arrange a premium contract with your support provider.

dnsjain · Post by **dnsjain** » Fri Apr 21, 2006 12:46 pm

Try this:

If IsNull(Trim(DSLink14.address2," ")) Or Trim(DSLink14.address2) = "" Then DSLink14.address3 Else DSLink14.address2

DSguru2B · Post by **DSguru2B** » Fri Apr 21, 2006 1:28 pm

check for two things,
1) whether its null or not
2) trim it and apply the len() function and see if its = 0

That should take care of it.