Examine the structure of the PROMOS table:
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;
Explanation:
The NULLIF Function
The NULLIF function tests two terms for equality. If they are equal the function returns a null, else
it returns the first of the two terms tested.
The NULLIF function takes two mandatory parameters of any data type. The syntax is
NULLIF(ifunequal,
comparison_term), where the parameters ifunequal and comparison_term are compared. If they
are identical, then NULL is returned. If they differ, the ifunequal parameter is returned
ANSWER A – date and String incompatibl;a datatypes for NVL function
The Date TRUNC Function
The date TRUNC function performs a truncation operation on a date value based on a specified
date precision format.
The date TRUNC function takes one mandatory and one optional parameter.
Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents
any value that can be implicitly converted into a date item. The date precision format parameter
specifies the degree of truncation and is optional. If it is absent, the default degree of truncation is
day. This means that any time component
a is also corect.NVL function test if first parameter is null then return second parameter. in this case when it;s same date (NULLIF(promo_start_date, promo_end_date)) then return ‘Same day’ so it’s ok
A isn’t correct, the prior commenter forgot that NVL requires both inputs be of the same type.
A and B are incorrect.
C is also correct:
WITH promos AS (SELECT 1 promo_id,
‘same day promo’ promo_name,
TRUNC (SYSDATE) promo_start_date,
TRUNC (SYSDATE) promo_end_date
FROM DUAL
UNION
SELECT 2 promo_id,
‘not ending so soon promo’ promo_name,
TRUNC (SYSDATE) promo_start_date,
NULL promo_end_date
FROM DUAL)
SELECT promo_name,
NVL2 (TO_CHAR (TRUNC (promo_end_date – promo_start_date)),
NULL,
‘Same day’)
obs
FROM promos
?
TO_CHAR(TRUNC(promo_end_date – promo_start_date)) returns “0” for same day values, which is evaluated as NOT NULL in NVL2 function…
A and B are incorrect, as “dog” mentioned above “NVL requires both of its parameters to be of the same datatype”.
C and D both work, however only D is correct
The problem with C is that it contains a
TRUNC(promo_end_date-promo_start_date)
As we know DATE also stores the time, so if the difference between these 2 dates is less than 1 day (even though they are on different dates) then this TRUNC returns a 0, giving us an incorrect result.
A and D are correct. Try them!
A is not correct.
If you try this code:
select NVL(NULLIF(trunc(sysdate), trunc(sysdate)), ‘Same Day’) from dual;
You will get error:
ORA-01858: a non-numeric character was found where a numeric was expected.
Second argument in NVL function should be date datatype so this works fine:
select NVL(NULLIF(trunc(sysdate), trunc(sysdate)), sysdate) from dual;
So, D is correct answer.
Answer C won’t give ‘Same Day’ at output because this code would return 0:
select TO_CHAR(TRUNC(trunc(sysdate)-trunc(sysdate))) from dual;
Cheers!