coalesce function

Database

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.

6 REPLIES
Junior Contributor

Re: coalesce function

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

Re: coalesce function

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

Junior Contributor

Re: coalesce function

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'.

Re: coalesce function

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.

Enthusiast

Re: coalesce function

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?

Junior Contributor

Re: coalesce function

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