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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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)
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
urshit_1983 wrote: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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.