passing one column value to another if it is null
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 5
- Joined: Tue Apr 04, 2006 1:15 am
- Location: Site
passing one column value to another if it is null
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..
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..
-
- Participant
- Posts: 15
- Joined: Wed May 26, 2004 6:30 am
-
- Participant
- Posts: 5
- Joined: Tue Apr 04, 2006 1:15 am
- Location: Site
-
- Participant
- Posts: 5
- Joined: Tue Apr 04, 2006 1:15 am
- Location: Site
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
"" 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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 5
- Joined: Tue Apr 04, 2006 1:15 am
- Location: Site
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 5
- Joined: Tue Apr 04, 2006 1:15 am
- Location: Site
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.