Re: joining on columns with null value

ignore this. I screwed up my test case. 


-------------- Original message -------------- 

> I have to join two tables. all of the columns can be null. However, if there 
> are 
> nulls and the rest of the fields are the same, I want to join to succeed.I 
> did a 
> test case where i assumed that if i used the following, I would get a record 
> returned even if there are nulls. 
> where nvl(tableA.column,0) = nvl(tableB.column,0) 
> 
> If both of these fields are null and i have the rest of the columns 
> equivalent, 
> this will not give me a record returned. 
> 
> However, if i do: 
> 
> where tableA.column1||tableA.column2||tableA.column3 = 
> tableB.column1||tableB.column2||tableB.column3 
> 
> then i get a successful join if one or more of the fields is null but atleast 
> 1 
> is not. 
> this is pretty ugly. Any better way to do this? 
> 
> -- 
> http://www.freelists.org/webpage/oracle-l 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: