Examine the structure of the PROMOS table:
name Null Type
PROMO_ID NOT NULL NUMBER(3)
PROMO_NAME VARCHAR2(30)
PROMO_START_DATE NOT NULL DATE
PROMO_END_DATE NOT NULL DATE
You want to display the list of promo names with the message ‘Same Day’ for promos that started and
ended on the same day.
Which query gives the correct output?
A.
SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), ‘Same Day’) FROM promos;
B.
SELECT promo_name, NVL(TRUNC(promo_end_date – promo_start_date), ‘Same Day’) FROM promos;
C.
SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL,’Same Day’)
FROM promos;
D.
SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL,’Same day’) FROM promos;
WHy is A not correct?
i think both A and D are right. Can anyone explain to me why A is not correct?
a is not correct b/c The data types of the original and ifnull
parameters must always be compatible
in order to correct choose ‘A’ use to_char like the following.
SELECT promo_name, NVL(to_char(NULLIF(promo_start_date, promo_end_date)), ‘Same Day’) FROM promos;
select item_no, nvl(nullif(start_date,end_date),’Same Day’) from ord_items;
ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 – “a non-numeric character was found where a numeric was expected”
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
And from documentation:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm
SELECT last_name, NVL(TO_CHAR(commission_pct), ‘Not Applicable’)
“COMMISSION” FROM employees
WHERE last_name LIKE ‘B%’
ORDER BY last_name;
LAST_NAME COMMISSION
————————- —————————————-
Baer Not Applicable
Baida Not Applicable
Banda .1
Bates .15
Bell Not Applicable
Bernstein .25
Bissot Not Applicable
Bloom .2
Bull Not Applicable
The answer D would only be correct if [hour:minute:second] portions of the start and end dates are the same. Otherwise the D would also be incorrect. Trunc function should be used to get the correct answer
Example:
select decode(nullif(sysdate,(sysdate -0.1)),null,’Same Day’) from dual
DECODE(NULLIF(SYSDATE,(SYSDATE-.
——————————–
select decode(nullif(trunc(sysdate),trunc((sysdate -0.1))),null,’Same Day’) from dual
DECODE(NULLIF(TRUNC(SYSDATE),TRU
——————————–
Same Day