Examine the structure of the SHIPMENTS table:
name Null Type
PO_ID NOT NULL NUMBER(3)
PO_DATE NOT NULL DATE
SHIPMENT_DATE NOT NULL DATE
SHIPMENT_MODE VARCHAR2(30)
SHIPMENT_COST NUMBER(8,2)
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the
SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
SQL> SELECT po_id, CASE
WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN
TO_CHAR((shipment_date – po_date) * 20) ELSE ‘No Penalty’ END PENALTY
FROM shipments;
SQL>SELECT po_id, DECODE
(MONTHS_BETWEEN (po_date,shipment_date)>1,
TO_CHAR((shipment_date – po_date) * 20), ‘No Penalty’) PENALTY
FROM shipments;
Which statement is true regarding the above commands?
A.
Both execute successfully and give correct results.
B.
Only the first query executes successfully but gives a wrong result.
C.
Only the first query executes successfully and gives the correct result.
D.
Only the second query executes successfully but gives a wrong result.
E.
Only the second query executes successfully and gives the correct result.
could you please explain why the second query does not gives correct result?
Because you cannot give condition as an expression in DECODE. So the first expression cannot be MONTHS_BETWEEN (po_date,shipment_date)>1
MONTHS_BETWEEN (po_date,shipment_date)>1
the difference of po_date and shipment_date less than 1 b\c the resalt in negative.
No, you cannnot use condiction in DECODE.
Hi Justyna, could you also help with some of the questions for 1Z0-047? (SQL expert) Thanks.
yes you cannot give condition as an expression in DECODE function.
SQL> select * from test;
ID
———-
1
2
3
4
SQL> select decode (id,2,’true’,’false’) from test;
DECOD
—–
false
true
false
false
SQL> select decode (id>2,2,’true’,’false’) from test;
select decode (id>2,2,’true’,’false’) from test
*
ERROR at line 1:
ORA-00907: missing right parenthesis
I have a question. After the CASE clause must be an expression, and in the example after the CASE is the word ‘WHEN’. Not this wrong ?
Oh, sorry. I was wrong
This article offers clear idea designed for the new users of blogging, that really how to do blogging.|