3 Replies Latest reply: Nov 23, 2012 9:36 AM by 789736 RSS

    nvl question (kind off)

      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

        • 1. Re: nvl question (kind off)
          Frank Kulash

          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.
          See the forum FAQ {message:id=9360002}
          • 2. Re: nvl question (kind off)
            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)
              Brilliant, Thanks very much for the help. I was not aware there was a nvl2 . This will come in very useful. Thanks :)