Date value null

Tools

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 using

select top 10 case when load_date is null then ' ' else load_date end from cust_prof_fct
but 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.

6 REPLIES
Teradata Employee

Re: Date value null

Teradata can't decide which data type you intended as the result
CASE WHEN ... THEN [VARCHAR] ELSE [DATE] END

Make 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

Re: Date value null

Thanks for your reply

i resolved this issue

select 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)) end
from CUST_PROF_FCT sample 10

Re: Date value null

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.

Sample:

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.

Teradata Employee

Re: Date value null

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

Re: Date value null

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

Teradata Employee

Re: Date value null

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.