Re: about nvl2 function

Giganews Newsgroups
Subject: Re: about nvl2 function
Posted by:  ddf (oratu…@msn.com)
Date: Tue, 8 Jun 2010

Comments embedded.
On Jun 8, 10:45=A0am, riverdance <esthershe.…@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.
>
> =A0I 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 =3D=3D 1 divided by 1 divided by 2010 =3D=3D  .000497512

1-1 2010 =3D=3D 1 minus 1 minus 2010 =3D=3D -2010

> SQL> select nvl2(birth_dt,0,1/1/2010) from eshen.test;
>
> NVL2(BIRTH_DT,0,1/1/2010)
> -------------------------
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.000497512
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.000497512
>
> SQL> select nvl2(birth_dt,0,1-1-2010) from eshen.test;
>
> NVL2(BIRTH_DT,0,1-1-2010)
> -------------------------
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -2010
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -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) =3D 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')))
-------------------------------------------------------------------------
=20
2455198
=20
0
=20
2455198
=20
0
=20
2455198
=20
0
=20
2455198
=20
0
=20
2455198
=20
0
=20
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')))
-------------------------------------------------------------------------
=20
2455198
=20
0
=20
2455198
=20
0
=20
2455198
=20
0
=20
2455198
=20
0
=20
2455198
=20
0
=20
2455198

....

100 rows selected.

SQL>

David Fitzjarrell

Replies

In response to

about nvl2 function posted by riverdance on Tue, 8 Jun 2010