Hello,
We have a flat file, F1 with say 2 columns, C1 and C2. We have another flat file F2 with two columns D1 and D2. If C2 is null, then we need to replace the null value with the value corresponding to D2 when D1 equals a particular value, say a.
The SQL code is
Update F1
set C2 = (select D2 from F2 where D1 = 'a')
where C2 is null.
If C2 is not null, we do not do anything. I can maybe use a lookup stage to do this (not sure, need to test) but I am not sure how I do not do anything if C2 is not null.
Thanks.
Help in subquery logic
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Insert a "C2 is null" filter into the data flow?
You haven't really thought about this as a DataStage problem. You have to read the entire text file. Therefore you will get to see every row. A conditional transformation will be easy to construct
You haven't really thought about this as a DataStage problem. You have to read the entire text file. Therefore you will get to see every row. A conditional transformation will be easy to construct
Code: Select all
If IsNull(C2) Then D2 Else originalvalue
Last edited by ray.wurlod on Wed Dec 21, 2011 8:17 pm, edited 1 time in total.
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.
Yes, Ray. I was thinking after my post and a lookup might not really work. Your code is fine but we need the value of D2 corresponding to D1 having a particular value.
Sure Pandeesh. Here's the example:
Flat file (F1) with two columns C1 and C2
Sure Pandeesh. Here's the example:
Flat file (F1) with two columns C1 and C2
- C1 C2
1 B
2 C
3 Null
4 D
- D1 D2
A ZZ
AA YY
- C1 C2
1 B
2 C
3 ZZ
4 D
The one way is think of is:(i know it's bigger in design)
a)Split out the records which has null in C2 and add a dummy column with value 1
b)Split out the A record alone from reference file and add a dummy column with value 1
c)join based on Dummy column and take the value corresponding to A and drop the unnecessary columns.
d)FInally combine using Funnel with the records which is not null in C2.
I hope it works.
a)Split out the records which has null in C2 and add a dummy column with value 1
b)Split out the A record alone from reference file and add a dummy column with value 1
c)join based on Dummy column and take the value corresponding to A and drop the unnecessary columns.
d)FInally combine using Funnel with the records which is not null in C2.
I hope it works.
pandeeswaran
Thanks Pandeesh. Let me think on your suggestion. I must add that I need perform the same operation on two other columns from the two flat files also.
So extending our earlier example:
Flat file (F1) with four columns C1,C2, C3 and C4
One of the values of C2 is null. So replace it with the value of D2 when D1 = 'A'. Repeat the process for C3 (replace it with value of D3 when D1='A')and C4 (replace if with value of D4 when D1 = 'A')also. So F1 will now have the following values:
So extending our earlier example:
Flat file (F1) with four columns C1,C2, C3 and C4
- C1 C2 C3 C4
1 B C D
2 C Null B
3 Null D C
4 D B Null
- D1 D2 D3 D4
A ZZ ZZZ ZZZZ
AA YY YYY YYYY
One of the values of C2 is null. So replace it with the value of D2 when D1 = 'A'. Repeat the process for C3 (replace it with value of D3 when D1='A')and C4 (replace if with value of D4 when D1 = 'A')also. So F1 will now have the following values:
- C1 C2 C3 C4
1 B C D
2 C ZZZ B
3 ZZ D C
4 D B ZZZZ