Examine the structure of the INVOICE table.
name Null Type
INV_NO NOT NULL NUMBER(3)
INV_DATE DATE
INV_AMT NUMBER(10,2)
Which two SQL statements would execute successfully? (Choose two.)
A.
SELECT inv_no,NVL2(inv_date,’Pending’,’Incomplete’)
FROM invoice;
B.
SELECT inv_no,NVL2(inv_amt,inv_date,’Not Available’)
FROM invoice;
C.
SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)
FROM invoice;
D.
SELECT inv_no,NVL2(inv_amt,inv_amt*.25,’Not Available’)
FROM invoice;
please kindly explain why A and C are correct.
I thought these are not the case where automatic typecasting should happen.
Option A – the function takes a DATE type, and Character datatype (’Pending’,’Incomplete’). It should be incorrect.
Option C – the function takes a DATE type, and a Numeric datatype (sysdate-inv_date). It should be incorrect.
OR, if auto-typecasting occurs, does it mean it is supposed to occur in Functions like NVL, NVL2?
please enlighten me a bit on this subject.
About A: there is no doubt because for the NVL2 function is important that second and third column have the same (compatible) data type, the type of the first column can be different.
About C: as the second and the third column data types have to be compatible, system tries to do conversion of the third column according to the type of the second one. In our example sysdate-inv_date is a number, a portion of a time, so the third column will be seen as a portion of a time, too (internal representation of Date type is like that, so it is possible). But, for example, the statement:
SELECT inv_no,NVL2(inv_date,sysdate,sysdate-some_date)
FROM invoice;
wouldn’t be correct.
Similar, the next two statements is ok, but the third one no:
SELECT employee_id,NVL2(commission_pct,1,’2′)
FROM employees;
SELECT employee_id,NVL2(commission_pct,’a’,1)
FROM employees;
SELECT employee_id,NVL2(commission_pct,1,’a’)
FROM employees;
Then why doesn’t SELECT NVL(5, sysdate) FROM DUAL; work?
Strange I find that SELECT NVL(sysdate-sysdate, sysdate) FROM DUAL; does work…
it wont work 5 and sysdate are in different format
u get error as
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
A is correct because “The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG”
C is correct because
“If the datatypes of expr2 and expr3 are different:
If expr2 is character data, then Oracle Database converts expr3 to the datatype of expr2 before comparing them unless expr3 is a null constant. In that case, a datatype conversion is not necessary. Oracle returns VARCHAR2 in the character set of expr2.
If expr2 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype. ”
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions106.htm
How it is correct, it shows
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
If expr2 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.
So in C, sysdate is converted to numeric type (sysdate-inv_date)? How does a DATE type get converted to a NUMBER type?
why option D is incorrect??
Why is D not correct?
D is wrong because exp 2 nad 3 are in different format
There is one Simple thing.
NVL2( value, ifnotnull, ifnull) .. In this ifnotNull and ifNull value should be of same data type.
if you are writing like this..
SQL> select nvl2(sysdate,’dont’,’append’) from dual
NVL2(S
——
dont
See there is one exception . If you are writing string first then its ok.
SQL> select NVL2(sysdate,’append’,1) from dual;
NVL2(S
——
append
But if you will write number before string than it will throw error.
SQL> select nvl2(sysdate,1,’append’) from dual;
ERROR at line 1:
ORA-01722: invalid number
It means Oracle Doesn’t convert string into number.
tested is C and ok… about A – you need to_char to make date char to be ok… others don’t work… sintax is not ok
and for D if you use number… ‘500’ for exmp, it will work…
SELECT employee_id, NVL2(to_char(hire_date),’pending’,’not available’)
FROM employees;
SELECT hire_date ,NVL2(to_char(job_id), to_char(hire_date), ‘Not Available’)
FROM employees;
SELECT employee_id, NVL2(hire_date,sysdate-hire_date,sysdate)
FROM employees;
SELECT employee_id,NVL2(salary,salary*.25,’500′)
FROM employees;
sintax for all to work!!!
i use sintax based on HR schema and employees table. using the question intax only !!!! C work…