1Z0-051 QUESTION 6 CASE和DECODE用法

QUESTION 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: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.

答案：C

解析：

case的语法参考ORACLE官方文档：

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/case_statement.htm

摘要如下：

  SELECT job_id INTO jobid from employees WHERE employee_id = empid;  CASE    WHEN jobid = 'PU_CLERK' THEN sal_raise := .09;    WHEN jobid = 'SH_CLERK' THEN sal_raise := .08;    WHEN jobid = 'ST_CLERK' THEN sal_raise := .07;    ELSE sal_raise := 0;  END CASE;

故，第一条语句执行成功，并返回正确结果。

decode的语法参考ORACLE官方文档：

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm#SQLRF00631

摘要如下：

SELECT product_id,       DECODE (warehouse_id, 1, 'Southlake',                              2, 'San Francisco',                              3, 'New Jersey',                              4, 'Seattle',                                'Non domestic')        "Location of inventory" FROM inventories       WHERE product_id < 1775;

故，第二句语法错误，无法执行。

具体如下：

SQL> --create tableSQL> create table shipments(  2  po_id number(3) not null,  3  po_date date not null,  4  shipment_date date not null,  5  shipment_mode varchar2(30),  6  shipment_cost number(8,2));表已创建。SQL> insert into shipments values(1,sysdate-2*365,sysdate,'1',23);已创建 1 行。SQL> insert into shipments values(2,sysdate-2,sysdate,'2',45);已创建 1 行。SQL> insert into shipments values(3,sysdate,sysdate,'3',67);已创建 1 行。SQL> select * from shipments;     PO_ID PO_DATE        SHIPMENT_DATE  SHIPMENT_MODE---------- -------------- -------------- -----------------------------SHIPMENT_COST-------------         1 28-12月-11     27-12月-13     1           23         2 25-12月-13     27-12月-13     2           45         3 27-12月-13     27-12月-13     3           67SQL> SELECT po_id, CASE  2  WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN  3  TO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PEN  4  FROM shipments;     PO_ID PENALTY---------- ----------------------------------------         1 14600         2 No Penalty         3 No PenaltySQL> SELECT po_id, DECODE  2  (MONTHS_BETWEEN (po_date,shipment_date)>1,  3  TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY  4  FROM shipments;(MONTHS_BETWEEN (po_date,shipment_date)>1,                                       *第 2 行出现错误:ORA-00907: 缺失右括号