All Forums Database
gtripathy 3 posts Joined 08/14
07 Aug 2014
coalesce function

Hi,
Not sure why the below statement throwing data type mismatch error..
Select coalesce( AP.RECORD_DATE,'Not yet recorded') as RECORD_DATE from SI.PRODUCT_TRANSACTION..
Can anyone suggest what's going wrong?
 
Thanks.

dnoeth 4628 posts Joined 11/04
07 Aug 2014

Assuming that AP.RECORD_DATE is a date, you're trying to combine a DATE and a VARCHAR.
As you can't turn 'Not yet recorded' into a valid date you must cast the date to a VarChar:

coalesce( CAST(AP.RECORD_DATE AS VARCHAR(10)),'Not yet recorded') as RECORD_DATE

 

Dieter

gtripathy 3 posts Joined 08/14
08 Aug 2014

Hi,
Thanks for the reply..However i intend to see the null records as 'Not yet recorded' in the RECORD_DATE column.But i don't see them actually..

dnoeth 4628 posts Joined 11/04
08 Aug 2014

What do you mean by "i don't see them", you must be more specific.
The COALESCE will return the DATE casted to a string or 'Not yet recorded'.

Dieter

gtripathy 3 posts Joined 08/14
08 Aug 2014

Select RECEIVE_DATE,coalesce(CAST(AP.RECORD_DATE as varchar(10)),'Not yet recorded') as RECORD_DATE from SI.PRODUCT_TRANSACTION..
This above statement is running wothout any error now.But what i wanted to see is string 'Not yet recorded',when there will be null in the AP.RECORD_DATE column. but thats not happening actually.
 
Thanks.

krishaneesh 140 posts Joined 04/13
08 Aug 2014

are there any NULL RECORD_DATE values in the table? Doen't seem that there are null in the record_date column.
Also not sure where you got the alias AP from. is this the complete query or missing a join?

dnoeth 4628 posts Joined 11/04
08 Aug 2014

Add a WHERE AP.RECORD_DATE IS NULL to check if there are any NULLs.

Dieter

You must sign in to leave a comment.