about nvl2 function
in [Oracle]
|
Prev: ORA-06502: PL/SQL: numeric or value error: character stringbuffer too small
Next: Multiple Requirements
From: riverdance on 8 Jun 2010 13:07 On Jun 8, 11:58 am, ddf <orat...(a)msn.com> wrote: > Comments embedded. > On Jun 8, 10:45 am, riverdance <esthershe...(a)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!
From: Andrew M on 9 Jun 2010 09:45 Another reason to use coalessce is that it will short circuit the parameters while nvl and nvl2 will not. This might seem trivial and is if your arguments are literals or columns. But if they are functions, sequences or some computed value it can make a difference in performance or correct data. sql->create or replace package atst 2 as 3 4 function getNextVal return number; 5 end; 6 / Package created. Elapsed: 00:00:00.02 sql->show err No errors. sql-> sql->-------------------------------------- sql->create or replace package body atst 2 as 3 4 x number; 5 6 function getNextVal return number 7 is 8 begin 9 x := x + 1; 10 return x; 11 end; 12 13 14 begin 15 x := 0; 16 end; 17 / Package body created. Elapsed: 00:00:00.02 sql->show err No errors. sql-> sql->col nxtval format 99999 sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual; NXTVAL ------ 1 Elapsed: 00:00:00.00 sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual; NXTVAL ------ 3 Elapsed: 00:00:00.00 sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual; NXTVAL ------ 5 Elapsed: 00:00:00.01 sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual; NXTVAL ------ 7 Elapsed: 00:00:00.00 sql-> sql->--============================================================ sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval from dual; NXTVAL ------ 9 Elapsed: 00:00:00.00 sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval from dual; NXTVAL ------ 10 Elapsed: 00:00:00.00 sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval from dual; NXTVAL ------ 11 Elapsed: 00:00:00.01 sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval from dual; NXTVAL ------ 12
First
|
Prev
|
Pages: 1 2 Prev: ORA-06502: PL/SQL: numeric or value error: character stringbuffer too small Next: Multiple Requirements |