Code Review Stack Exchange is a question and answer site for peer programmer code reviews. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


  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;
share|improve this question
up vote 4 down vote accepted

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
          end chicken_hatch_date              
   from   stricken_chicken;

work for you?

share|improve this answer
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?

        WHEN chicken_hatch_date IS NULL THEN
    END AS "chicken_hatch_date"           
share|improve this answer
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;


  • Corrected . to ,
  • Corrected order of parameters to nvl2()
share|improve this answer
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;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.