CODEWITHSUNDEEP
oracle-databasedate
IbrahimMitko
IbrahimMitko

Reputation: 1207

Oracle - Inconsistent datatype error when checking for null Date

I'm trying to write a case for when a Date is null but I keep getting this error:

ORA-00932: inconsistent datatypes: expected CHAR got DATE
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

The code is pretty simple. Is there a different way to check for null when comparing to a Date?

case
    when cr.COMPLETED_DATE is null then '--'
    else cr.COMPLETED_DATE
  end completed_date,

Upvotes: 2

Views: 1095

Answers (2)

Praveen
Praveen

Reputation: 9335

Here the issue is with the return values from the case when statement

cr.COMPLETED_DATE is null then '--'

returns string datatype and

else cr.COMPLETED_DATE

returns a date datatype, hence the error

inconsistent datatypes: expected CHAR got DATE

One work around is to change the datatype of second case condition to char

case
    when cr.COMPLETED_DATE is null then '--'
    else to_char(cr.COMPLETED_DATE, 'dd-mm-yyyy')
end completed_date,

or use a date like '1900-01-01' when the date is null like

case
    when cr.COMPLETED_DATE is null then Date '1900-01-01'
    else cr.COMPLETED_DATE
end completed_date,

Upvotes: 5

Alex Poole
Alex Poole

Reputation: 191275

The two branches of your case expression get different data types. If the column is null you're providing a string, '--'. If it is not null then you are using the original date. That is what the error message is telling you.

If you want the null case to use a string then the not-null case also has to produce a string, so you'll have to convert your date to a string, using a suitable format model (don't rely on NLS settings), for example:

case
    when cr.COMPLETED_DATE is null then '--'
    else to_char(cr.COMPLETED_DATE, 'YYYY-MM-DD')
  end completed_date,

Quick demo with a CTE:

with cr (completed_date) as (
  select date '2016-09-23' from dual
  union all select null from dual
)
select
case
    when cr.COMPLETED_DATE is null then '--'
    else to_char(cr.COMPLETED_DATE, 'YYYY-MM-DD')
  end completed_date
from cr;

COMPLETED_
----------
2016-09-23
--

The result of the case expression is a string for both rows.

You could do the same thing with

nvl(to_char(cr.COMPLETED_DATE, 'YYYY-MM-DD'), '--') completed_date,

but case is more portable and often clearer.

Upvotes: 0

Related Questions