Re: about nvl2 function

From: riverdance <esthershensh_at_yahoo.com>
Date: Tue, 8 Jun 2010 10:07:51 -0700 (PDT)
Message-ID: <576a09a3-0550-4f0f-b4c0-ff32890d862f_at_j8g2000yqd.googlegroups.com>

On Jun 8, 11:58 am, ddf <orat..._at_msn.com> wrote:
> Comments embedded.
> On Jun 8, 10:45 am, riverdance <esthershe..._at_yahoo.com> wrote:
>
> > HI,
>
> > sorry , I'm new to oracle world..
>
> No need to apologize.
>
> > 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?
>
> They're not strings, they are numeric calculations:
>
> 1/1/2010 == 1 divided by 1 divided by 2010 ==  .000497512
>
> 1-1 2010 == 1 minus 1 minus 2010 == -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
>
> Date strings would be '1/1/2010' or '1-1-2010', including the single
> quotes, and would be used with the to_date function:
>
> SQL> create table test(name  varchar2(30), birth_dt date);
>
> Table created.
>
> SQL>
> SQL> begin
>   2          for i in 1..100 loop
>   3                  if mod(i,2) = 0 then
>   4                  insert into test(name, birth_dt)
>   5                  values('Narmo'||i, sysdate - (10*i));
>   6                  else
>   7                  insert into test(name)
>   8                  values('Narmo'||i);
>   9                  end if;
>  10          end loop;
>  11
>  12          commit;
>  13  end;
>  14  /
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> --
> SQL> -- These error out with proper dates as return values
> SQL> --
> SQL> select nvl2(birth_dt,0,to_date('1/1/2010', 'mm/dd/rrrr')) from
> test;
> select nvl2(birth_dt,0,to_date('1/1/2010', 'mm/dd/rrrr')) from test
>                        *
> ERROR at line 1:
> ORA-00932: inconsistent datatypes: expected NUMBER got DATE
>
> SQL> select nvl2(birth_dt,0,to_date('1-1-2010', 'mm-dd-rrrr')) from
> test;
> select nvl2(birth_dt,0,to_date('1-1-2010', 'mm-dd-rrrr')) from test
>                        *
> ERROR at line 1:
> ORA-00932: inconsistent datatypes: expected NUMBER got DATE
>
> SQL> --
> SQL> -- These don't error but don't return the value you wanted
> SQL> --
> SQL>
> SQL> select nvl2(birth_dt,sysdate,to_date('1/1/2010', 'mm/dd/rrrr'))
> from test;
>
> NVL2(BIRT
> ---------
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
>
> NVL2(BIRT
> ---------
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
>
> ...
> 100 rows selected.
>
> SQL> select nvl2(birth_dt,sysdate,to_date('1-1-2010', 'mm-dd-rrrr'))
> from test;
>
> NVL2(BIRT
> ---------
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
>
> NVL2(BIRT
> ---------
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
> 01-JAN-10
> 08-JUN-10
>
> ...
>
> 100 rows selected.
>
> SQL>
> SQL> --
> SQL> -- These give you the 0 you want but have more gyrations to
> return the date you coded and do so in Julian format
> SQL> -- as that 'date' is actually a number
> SQL> --
> SQL> select nvl2(birth_dt,0,to_number(to_char(to_date('1/1/2010', 'mm/
> dd/rrrr'),
>  'J'))) from test;
>
> NVL2(BIRTH_DT,0,TO_NUMBER(TO_CHAR(TO_DATE('1/1/2010','MM/DD/
> RRRR'),'J')))
> -------------------------------------------------------------------------
>
> 2455198
>
> 0
>
> 2455198
>
> 0
>
> 2455198
>
> 0
>
> 2455198
>
> 0
>
> 2455198
>
> 0
>
> 2455198
>
> ...
>
> 100 rows selected.
>
> SQL> select nvl2(birth_dt,0,to_number(to_char(to_date('1-1-2010', 'mm-
> dd-rrrr'),
>  'J'))) from test;
>
> NVL2(BIRTH_DT,0,TO_NUMBER(TO_CHAR(TO_DATE('1-1-2010','MM-DD-
> RRRR'),'J')))
> -------------------------------------------------------------------------
>
> 2455198
>
> 0
>
> 2455198
>
> 0
>
> 2455198
>
> 0
>
> 2455198
>
> 0
>
> 2455198
>
> 0
>
> 2455198
>
> ...
>
> 100 rows selected.
>
> SQL>
>
> David Fitzjarrell

David, really apprecated! Received on Tue Jun 08 2010 - 12:07:51 CDT