Join on Null

[vsi]

I want to join two tables TableA, TableB

Code: Select all

      A.Col1=B.Col1
  and A.Col2=B.Col2

The thing is that I should allow for the join on one condition when other condition fails due to NULLSi.e. say A.Col1 and B.Col1 both are NULL.

I tried with NVL in join condition. But the statement hangs up. Whats the most efficient way

[urshit_1983]

Use

Cats(A.Col=B.Col ) = sv1
If IsNull(sv1) Then sv1 Else @NULL = sv2


Thanks

[Krazykoolrohit]

check if A.Col1 is nul and riase a flag. similary check if B.Col1 is null and raise another flag.

Do a join only if both the flags are up.

[vsi]

how can i achieve that purely on SQL

[chulett]

You are looking for a SQL solution, I assume, and not a DataStage one. You haven't specified a database - Oracle?

[urshit_1983]

Ok then in input stage of Database select the option

Generate Select statement and enter other clauses.

There just give the join statement

where A.columnname=B.columnname is Null

[DSguru2B]

look into the Collasce function. You can use that in the where predicates. I might be off by a word or two in the name of the function. I have used that before in my sql queries. What it basicaly does is checks if the key is null, it will replace it with a default value that you specify. The format is
Collasce(ColA, DefaultValue). Look into it, that might help.

[vsi]

a) Is coalesce more efficient than nvl()
b) As its not necessary to join when both columns of same table (i.e. A.Col1 and A.Col2) are NULL, then is Case(i) more efficient or Case (ii)

Case (i)

Code: Select all

select * 
from TableA A

inner join Table B B 
on coalesce(A.Col1,'')=coalesce(B.Col2,'')
and coalesce(A.Col2,'')=coalesce(B.Col2,'')

Case (ii)

Code: Select all

select * 
from TableA A

inner join Table B B 
on coalesce(A.Col1,'')=coalesce(B.Col2,'')
and coalesce(A.Col2,'')=coalesce(B.Col2,'')

where (coalesce(A.Col1,0) and coalesce(A.Col2,0)) =1 

[ray.wurlod]

Cats(A.Col=B.Col ) = sv1
If IsNull(sv1) Then sv1 Else @NULL = sv2

It is not permissible to assign to the @NULL system variable as you have done. I suspect you are trying to indicate "assign to". Maybe ==> would have been a better symbol.

What are you trying to achieve with the Cats() function? As you have written it, it can only ever return 1 or 0, and is not required. The expression A.Col=B.Col will return exactly the same result.