Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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;
share|improve this question

4 Answers 4

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
             'N'
          else
             'Y'
          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?

SELECT
    stricken_chicken_id,
    CASE 
        WHEN chicken_hatch_date IS NULL THEN
            'N'
        ELSE
            'Y'
    END AS "chicken_hatch_date"           
FROM
    stricken_chicken;
share|improve this answer
1  
Won't this always choose 'Y'? –  kyooryu Mar 25 at 11:41
    
Well spotted - moved the variable so that the statement becomes IS NULL –  John D Mar 25 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()
share|improve this answer
    
This will not work properly. –  kyooryu Mar 25 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 at 16:50
1  
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 at 18:35
    
I've swapped the order of the parameters in Rev 3. –  200_success Mar 25 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

 
discard

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.