6. 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:
(題意:題目給了一個發貨表Shipments,其中有PO_DATE和SHIPMENT_DATE 欄位,如果SHIPMENT_DATE比PO_DATE遲一個月,則每多一天罰款20,對此,請評價下面給出的兩個sql語句)
SQL> SELECT po_id, CASEWHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THENTO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PENALTYFROM 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.
Answer: C
decode函數的文法是,decode(條件,值1,返回值1,值2,返回值2,...值n,返回值n,預設值)
對比,上面decode使用錯誤。Case...When的文法是正確的,並且語句寫的也是正確的,可以得到正確的結果。
第二個decode文法可以這樣改,使用sign函數判斷大小。
1 SELECT po_id, DECODE2 (SIGN(MONTHS_BETWEEN(po_date,shipment_date)),1 3 TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY4 FROM shipments;