# Simplify Oracle SQL: Treat null date as 'N' and non-null date as 'Y'

This works, but nvl AND case seem redundant. Basically, if null then 'N' else 'Y.

CHICKEN_HATCH_DATE is a DATE data type.

  select  stricken_chicken_id,
case nvl(to_char(chicken_hatch_date),'N')
when 'N' then 'N'
else 'Y'
end chicken_hatch_date
from   stricken_chicken;

-

I'm not sure what's wrong with your code, but if you don't like all of those 'N''s would

  select  stricken_chicken_id,
case when to_char(chicken_hatch_date) is null then
'N'
else
'Y'
end chicken_hatch_date
from   stricken_chicken;


work for you?

-
I tweaked this slightly.. yielding select id, case when hatch_date is null then 'N' else 'Y' end .... There is no need to perform any operation on hatch_date. – zundarz Jun 26 '12 at 1:34

If your aim is to print N for a null date why not check that directly?

SELECT
stricken_chicken_id,
CASE
WHEN chicken_hatch_date IS NULL THEN
'N'
ELSE
'Y'
END AS "chicken_hatch_date"
FROM
stricken_chicken;

-
Won't this always choose 'Y'? – kyooryu Mar 25 '14 at 11:41
Well spotted - moved the variable so that the statement becomes IS NULL – John D Mar 25 '14 at 13:23

IMHO, nvl2 is easier to read:

select stricken_chicken_id,
nvl2(chicken_hatch_date, 'Y', 'N') chicken_hatch_date
from stricken_chicken;


Updated:

• Corrected . to ,
• Corrected order of parameters to nvl2()
-
This will not work properly. – kyooryu Mar 25 '14 at 11:38
@kyooryu I fixed a typo, the period after stricken_chicken_id should have been a comma. With that correction, this does work. – Devon_C_Miller Mar 25 '14 at 16:50
It will, however 'Y' and 'N' are mixed up - N should come when chicken_hatch_date is null, in your query it will be the other way round. – kyooryu Mar 25 '14 at 18:35
I've swapped the order of the parameters in Rev 3. – 200_success Mar 25 '14 at 20:38

I think that better approach is to use "decode" instead of "case" in this example:

select stricken_chicken_id
,decode(chicken_hatch_date, null, 'N', 'Y') chicken_hatch_date
from stricken_chicken;

-