See the Exhibit and examine the structure of the PROMOSTIONS table:
Which SQL statements are valid? (Choose all that apply.)
A.
SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost,
promo_cost * 0.25, 100) “Discount”
FROM promotions;
—B. SELECT promo_id, DECODE(promo_cost, 10000,
DECODE(promo_category, ‘G1’, promo_cost *.25, NULL),
NULL) “Catcost”
FROM promotions;
C.
SELECT promo_id, DECODE(NULLIF(promo_cost, 10000),
NULL, promo_cost*.25, ‘N/A’) “Catcost”
FROM promotions;
D.
SELECT promo_id, DECODE(promo_cost, >10000, ‘High’,
<10000, ‘Low’) “Range”
FROM promotions;
Explanation:
The DECODE Function
Although its name sounds mysterious, this function is straightforward. The DECODE function
implements ifthen-else conditional logic by testing its first two terms for equality and returns the
third if they are equal and optionally returns another term if they are not.
The DECODE function takes at least three mandatory parameters, but can take many more. The
syntax of the function is DECODE(expr1,comp1, iftrue1,
[comp2,iftrue2…[ compN,iftrueN]], [iffalse]).
can anyone explain about the mistake in C and D?
DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.
Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result.
So here in C it has problem to convert ‘NA’ to promo_cost*.25 datatype, which is number, I think.
In D I think 10000 is simply not correct Oracle syntax.
10000
Oh, man, I can’t enter comparison characters in my post, it’s removed automatically…:)