OCP-1Z0-051 第104題 DECODE,NVL,COALESCE函數用法

一、原題

Examine the structure of the PROMOS table:

Name                      Null              Type

PROMO_ID                NOT NULL        NUMBER(3)

PROMO_NAME                              VARCHAR2(30)

PROMO_START_DATE        NOT NULL        DATE

PROMO_END_DATE                          DATE

You want to generate a report showing promo names and their duration (number of days). If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed.

Which queries give the correct output? (Choose all that apply.)

A.SELECT promo_name,TO_CHAR(NVL(promo_end_date-promo_start_date,'ONGOING'))

       FROM promos;

B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date-promo_start_date),'ONGOING')

       FROM promos;

C.SELECT promo_name,NVL(TO_CHAR(promo_end_date-promo_start_date),'ONGOING')

      FROM promos;

D. SELECT promo_name,DECODE(promo_end_date-promo_start_date,NULL,'ONGOING',

                                                          promo_end_date-promo_start_date)

        FROM promos;

E. SELECT promo_name,decode(coalesce(promo_end_date,promo_start_date),null,'ONGOING',

                                                       promo_end_date - promo_start_date))

       FROM promos;

答案：BCD

二、題目翻譯

檢視表結構

顯示promo names和their duration，如果PROMO_END_DATE沒有值，則顯示'ONGOING'。

哪個查詢能得出正确的結果(選擇所有正确選項)？

三、題目解析

A選項不正确，因為隐式轉換'ONGOING'為數值類型的會報錯。

E選項不正确，不能輸出正确的結果。

NVL和NULLIF，COALESCE的用法，詳見：

         http://blog.csdn.net/holly2008/article/details/25251513

DECODE的用法，詳見：

         http://blog.csdn.net/holly2008/article/details/23140591