joins based on conditions (SQL)

[vsi]

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

[DSguru2B]

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

Code: Select all

cond: If NOT(hashedfile1.NOTFOUND) OR NOT(hashedfile2.NOTFOUND) then 'PASS' else 'REJ'

In the constraint of the output link specify

Code: Select all

cond = 'PASS'

Control the null values while loading the hashed files. That should get you your desired result if i understood your requirement correctly.

[dprasanth]

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

Asif,If I were you, I will try a routine like this
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.

[DSguru2B]

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

[dprasanth]

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

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 .

That is the reason why i gave a round about solution of using routine.

[vsi]

sorry if i didnt mention properly, I needed this in SQL, because i am doing the extraction based on these joins.

Regards

[DSguru2B]

How many coloumns do both the tables have?
What are the keys?
Are the tables identical?

[vsi]

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

[DSguru2B]

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.