97.View the Exhibit and evaluate the structure and data in the CUST_STATUS table.
You issue the following SQL statement:
SQL> SELECT custno, NVL2(NULLIF(amt_spent, credit_limit), 0, 1000 )"BONUS" FROM cust_status;
Which statement is true regarding the execution of the above query?
A.It produces an error because the AMT_SPENT column contains a null value.
B.It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
C.It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null .
D.It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.
答案:C
解析:
nullif(expr1,expr2):如果相等,那么返回null,如果不相等那么返回expr1,类型必须相同,expr1不能为null
nvl2用法参考98题,选择C
You issue the following SQL statement:
SQL> SELECT custno, NVL2(NULLIF(amt_spent, credit_limit), 0, 1000 )"BONUS" FROM cust_status;
Which statement is true regarding the execution of the above query?
A.It produces an error because the AMT_SPENT column contains a null value.
B.It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
C.It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null .
D.It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.
答案:C
解析:
nullif(expr1,expr2):如果相等,那么返回null,如果不相等那么返回expr1,类型必须相同,expr1不能为null
nvl2用法参考98题,选择C