Re: about nvl2 function

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 8 Jun 2010 09:08:29 -0700 (PDT)
Message-ID: <b47af01a-1206-411c-b761-f0683b17574f_at_i31g2000yqm.googlegroups.com>

On Jun 8, 10:52 am, "S. Anthony Sequeira" <nob..._at_127.0.0.1> wrote:
> On 08/06/10 15:45, riverdance wrote:
>
>
>
>
>
> > HI,
>
> > sorry , I'm new to oracle world..
>
> > who could help explain following.. I expected following SQL would
> > error out.. but I got following result.
>
> >   I don't understand, how date format string '1/1/2010' could convert
> > to number .000497512, and how date format string 1-1-2010 could
> > convert to number -2010?
>
> > SQL>  select nvl2(birth_dt,0,1/1/2010) from eshen.test;
>
> > NVL2(BIRTH_DT,0,1/1/2010)
> > -------------------------
> >                 .000497512
> >                 .000497512
>
> > SQL>  select nvl2(birth_dt,0,1-1-2010) from eshen.test;
>
> > NVL2(BIRTH_DT,0,1-1-2010)
> > -------------------------
> >                      -2010
> >                      -2010
>
> Do the math, look at date formats in the SQL Language reference, also
> see the TO_DATE function.
>
> 1 / 1 / 2010 = .0004975124
> 1 - 1 - 2010 = -2010
>
> --
> S. Anthony Sequeira
> ++
> i'm living so far beyond my income that we may almost be said to be
> living apart.
>                 -- e. e. cummings
> ++- Hide quoted text -
>
> - Show quoted text -

Yes, in hind site the answer should be obvious. I will suggest replacing NVL2 with the ANSI standard coalesce function which will return the first non-null value in a list of expressions:

SQL> select coalesce(to_char(sysdate,'MM/DD/YYYY'),'01/02/2010'),   2 coalesce(to_char(null,'MM/DD/YYYY'),'01/01/2010')   3 from dual;

COALESCE(T COALESCE(T
---------- ----------
06/08/2010 01/01/2010

HTH -- Mark D Powell -- Received on Tue Jun 08 2010 - 11:08:29 CDT