The PRODUCTS table has the following structure:
name Null Type
PROD_ID NOT NULL NUMBER(4)
PROD_NAME VARCHAR2(25)
PROD_EXPIRY_DATE DATE
Evaluate the following two SQL statements:
SQL>SELECT prod_id, NVL2(prod_expiry_date, prod_expiry_date + 15,”)
FROM products;
SQL>SELECT prod_id, NVL(prod_expiry_date, prod_expiry_date + 15)
FROM products;
Which statement is true regarding the outcome?
A.
Both the statements execute and give different results.
B.
Both the statements execute and give the same result.
C.
Only the first SQL statement executes successfully.
D.
Only the second SQL statement executes successfully.
NVL2(expr1,expr2,expr3)
if expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.
Is the first sql statement correct?pls help
i don’t think 1st query is correct
How will this statement execute?
SELECT prod_id, NVL2(prod_expiry_date, prod_expiry_date + 15,”)
FROM products;
Im getting the below error
ORA-01740: missing double quote in identifier
I think they are single quotes in the question ”
ya 1st query is wrong if we put null instead of ” then it will work or else it wont
so answer is D
Answer A is correct assuming the exp3 is ” not “.
Answer D is correct as ” is implicitly convert to null.
If you set ‘A’ it will throw a error.
Answer A is correct as ” is implicitly convert to null.
NVL2(prod_expiry_date, prod_expiry_date + 15,”)
If you set ‘A’ it will throw a error.
Sorry for the mistake 🙁
Answer A is Correct
SQL> select prod_id, nvl2(prod_expiry_date, prod_expiry_date + 15,”) from test1;
PROD_ID NVL2(PROD
———- ———
1 24-FEB-14
2 25-FEB-14
3
4
5 27-FEB-14
SQL> select prod_id, nvl(prod_expiry_date, prod_expiry_date + 15) from test1;
PROD_ID NVL(PROD_
———- ———
1 09-FEB-14
2 10-FEB-14
3
4
5 12-FEB-14
B
sorry correct Answer is A