This discussion is archived
3 Replies Latest reply: Nov 23, 2012 7:36 AM by 789736 RSS

nvl question (kind off)

789736 Explorer
Currently Being Moderated
Hi guys,

I was wondering if you could help me out please.

I have a table with a start_date and end_date as well as a temp_start_date and temp_end_date column.

What I want to do is display the start_date and end_date BUT, if the start_date is null I want to show the temp_start_date and temp_end_date in that column.

I was thinking I could use nvl or possibly decode to do this?

The first bit I think is straight forward, I nvl the start_date to the temp_start_date, but im not sure how to proceed from there? I only want to show the temp_end_date if the start_date is null. Any help on this would be greatly appreciated.

Select nvl(start_date,temp_start_date), end_date
From my_table

Thanks
  • 1. Re: nvl question (kind off)
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Use the NVL<b>2</b> function for that
    SELECT     NVL ( start_date
             , temp_start_date
             )          AS s_date
    ,     NVL2 ( start_date
              , end_date
              , temp_end_date
              )             AS e_date
    FROM    table_x
    ;
    You could use DECODE or CASE to test if start_date is NULL, and, depending on the answer, return either end_date or temp_end_date, but NVL2 was designed specifcally to do that. That is, the NVL2 expression above is equivalent to
    ,     CASE
             WHEN  start_date  IS NOT NULL
             THEN  end_date
             ELSE  temp_end_date
         END             AS e_date
    and also
    ,     DECODE ( start_date
                , NULL     , temp_end_date
                           , end_date
                )        AS e_date
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 2. Re: nvl question (kind off)
    spajdy Pro
    Currently Being Moderated
    There is also function coalesce. It take N parameters and result is first NOT NULL parameter. When all parameters are NULL then result is NULL.
  • 3. Re: nvl question (kind off)
    789736 Explorer
    Currently Being Moderated
    Brilliant, Thanks very much for the help. I was not aware there was a nvl2 . This will come in very useful. Thanks :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points