DSXchange

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

Hi,

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

HTH

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.

What is the data type for the field you specified?

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

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

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.

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

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

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

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.

Try this:

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

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.