All Forums Tools
sharma03 4 posts Joined 10/08
09 Oct 2008
Date value null

Hi All,I have date values null in load_date column and it is represented as '?'I want to to replace '?' values with ' '(space)here is the syntax i am usingselect top 10 case when load_date is null then ' ' else load_date end from cust_prof_fctbut when i run this query i am getting an error 3800:Data type mismatch in THEN/ELSE expression.please help me in resolving this issue.

Fred 1096 posts Joined 08/04
09 Oct 2008

Teradata can't decide which data type you intended as the result CASE WHEN ... THEN [VARCHAR] ELSE [DATE] ENDMake the datatypes match. For example:case when load_date is null then ' ' else CAST(load_date as CHAR(10)) end - or -case when load_date is null then date'0001-01-01' else load_date end

sharma03 4 posts Joined 10/08
09 Oct 2008

Thanks for your replyi resolved this issueselect case when(cast((LEC_STRT_DATE(format 'YYYY-MM-DD')) as char(10)) is null) then '' else cast((LEC_STRT_DATE(format 'YYYY-MM-DD')) as char(10)) endfrom CUST_PROF_FCT sample 10

bhanu01 3 posts Joined 02/16
21 Feb 2016

Hi ,
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.

Fred 1096 posts Joined 08/04
22 Feb 2016

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

bhanu01 3 posts Joined 02/16
22 Feb 2016

Thankyou Fred. :) Awesome..!  Now my Problem is resolved.

ToddAWalter 316 posts Joined 10/11
23 Feb 2016

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.

You must sign in to leave a comment.