joins based on conditions (SQL)
Moderators: chulett, rschirm, roy
joins based on conditions (SQL)
Hi all,
I want to join two tables, say A & B, based on conditions. Say
a) A.col1=B.col1 (if both not null) else
b) A.col2=B.col2 (if both not null) else
c)...
e) nothing matches then dont join (i.e. inner join)
can u help me out
Regards,
Asif
I want to join two tables, say A & B, based on conditions. Say
a) A.col1=B.col1 (if both not null) else
b) A.col2=B.col2 (if both not null) else
c)...
e) nothing matches then dont join (i.e. inner join)
can u help me out
Regards,
Asif
Last edited by vsi on Tue May 26, 2009 8:31 am, edited 2 times in total.
Put both those in a hashed file.
One hashed file should have col1 as key and the the second hashed file should have col2 as key specified.
Use a stage variable say cond
In the constraint of the output link specify
Control the null values while loading the hashed files. That should get you your desired result if i understood your requirement correctly.
One hashed file should have col1 as key and the the second hashed file should have col2 as key specified.
Use a stage variable say cond
Code: Select all
cond: If NOT(hashedfile1.NOTFOUND) OR NOT(hashedfile2.NOTFOUND) then 'PASS' else 'REJ'
Code: Select all
cond = 'PASS'
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Re: joins based on conditions (SQL)
Asif,If I were you, I will try a routine like thisvsi wrote:Hi all,
I want to join two tables, say A & B, based on conditions. Say
a) A.col1=B.col1 (if both not null) else
b) A.col2=B.col2 (if both not null) else
c)...
e) nothing matches then dont join (i.e. inner join)
can u help me out
Regards,
Asif
Begin case
case Not(Isnll(acol1)) and Not(Isnull(bcol1))
Ans="1"
case Not(Isnll(acol2)) and Not(Isnull(bcol2))
Ans="2"
Case Not(Isnll(acol3)) and Not(Isnull(bcol3))
Ans="3"
Case @True;
Ans="10"
I will derive a stage variable with this routine and I will have links for each value
So if stagevar="1"(put this in the constraint for each link)
I will have an OCI stage that will do a join with col1 of A and B. If the value is two there will be another stage that will have a join with col2 of A and B
I didn't give a try but just trying to figure out whether this logic may work.So your routine should have around 10 variables passed to it.
Always have a sort of aversion towards Hash files :D .. Never got a chance to work with Hash files-may be that is the reason for my dislike .DSguru2B wrote:Well no need to write a routine. Null handling can be done with in the transformer itself (for the source) and in the sql select (for building hashed files).
That is the reason why i gave a round about solution of using routine.
both are different tables. one has some 17 columns and the other some 10.
the thing is that we dont have one certain unique key or composite key. We are trying to eliminate the duplicates, based on this type of join i.e.
a) A.col1=B.col1 (if both not null) else
b) A.col2=B.col2 (if both not null) else
c)...
e) nothing matches then dont join (i.e. inner join)
if there happens any join, then the duplicates get eliminated
the thing is that we dont have one certain unique key or composite key. We are trying to eliminate the duplicates, based on this type of join i.e.
a) A.col1=B.col1 (if both not null) else
b) A.col2=B.col2 (if both not null) else
c)...
e) nothing matches then dont join (i.e. inner join)
if there happens any join, then the duplicates get eliminated
I dont think doing a join like that on a sql is going to be optimal. If the number of rows is rather small in both the tables, i advise you to use the method i advised earlier, by using hashed files. Because an OR in a where clause, that too during a join is rather expensive.
My 2cents.
My 2cents.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.