I have to compare the records of one table(A_Tab) with records of another table(B_Tab).
I used case statements to proceed in to this.
Sel A_Tab.ABC_TS, B_Tab.DEF_TS Case when CAST(A_Tab.ABC_TS as char(20)) = CAST(B_Tab.DEF_TS as Char(20)) then 'Pass' else 'Fail' end as DEF_TS,
A_Tab.GHI_DT,B_Tab.JKL_DT Case when A_Tab.GHI_DT = B_Tab.JKL_DT then 'Pass' else 'Fail' end as JKL_DT
from A_Tab join B_Tab on A_Tab.U1=B_Tab.U1.
ABC_TS, DEF_TS :- Timestamp(6) Columns contains Null Values.
GHI_DT, JKL_DT :- Date Columns Contains Null Values.
The above query is working and when I see the results I can see in this way:
When two dates or Timestamps matching then I am getting it as 'Pass' (I am okay with this)
But when null values are matched then the result is 'Fail'.
Can anyone help me on this.
the Null values should be replaced with '?' and when I compare the null values of colums of two tables i should get the result as as 'Pass' .
I tried with Coalesce but I getting the error as DATA type mismatch in THEN/ELSE expression.
I tried with CAST and the error is Syntax error: Expected something between ')' and '='
Can Any one help me in solving this problem.
Add another WHEN clause:
CASE WHEN a = b THEN 'Pass' WHEN a IS NULL AND b IS NULL then 'Pass' ELSE 'Fail' END
Or use a compound condition:
CASE WHEN (a=b) OR (a IS NULL AND b IS NULL) then 'Pass' ELSE 'Fail' END
To add a bit of ?why? to Fred's excellent answer...
CAST to character does not result in '?' for NULL values. Any function, arithmetic,... on NULL values results in a NULL value. So the result of the CAST above is NULL not '?'. Then the result of the comparison is then false because NULL cannot be equal to anything.
COALESCE should work, but you would need to specify a timestamp value to substitute when the column is NULL. And you would have to COALESCE both sides of the comparison. I like Fred's solution better since it is more obvious what is being done.
Also there is no need to do the CAST to CHAR in order to do the compare. Just let the system compare the timepstamps. It costs extra to CAST things if it is not necessary.