Help in subquery logic

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
pdntsap
Premium Member
Premium Member
Posts: 107
Joined: Mon Jul 04, 2011 5:38 pm

Help in subquery logic

Post by pdntsap »

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.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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

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.
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

Can you please post the sample input and output you require?
pandeeswaran
pdntsap
Premium Member
Premium Member
Posts: 107
Joined: Mon Jul 04, 2011 5:38 pm

Post by pdntsap »

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
  • C1 C2
    1 B
    2 C
    3 Null
    4 D
Flat file (F2) with two columns D1 and D2
  • D1 D2
    A ZZ
    AA YY
One of the values of C2 is null. So replace it with the value of D2 when D1 = 'A'. So F1 will now have the following values:
  • C1 C2
    1 B
    2 C
    3 ZZ
    4 D
Thanks.
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

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.
pandeeswaran
pdntsap
Premium Member
Premium Member
Posts: 107
Joined: Mon Jul 04, 2011 5:38 pm

Post by pdntsap »

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
  • C1 C2 C3 C4
    1 B C D
    2 C Null B
    3 Null D C
    4 D B Null
Flat file (F2) with four columns D1,D2,D3, and D4
  • 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
Thanks.
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

The idea is as long as same regardless of no of columns.
pandeeswaran
Post Reply