You Asked

Below is the question asked in 1Z0-051 exam.

View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table,
you need to find out the average cost for all promos in the ranges $0-2000 and $2000-5000 in category A
You issue the following SQL statement:

SQL>SELECT AVG(CASE
WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='A' then promo_cost
ELSE null END) "CAT_2000A",
AVG(CASE
WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='A' THEN promo_cost
ELSE null END) "CAT_5000A"
FROM promotions;
What would be the outcome?

it says that statement executes without a error. But i have read that NULL literal can not be used in return_expr of CASE expr. Shouldn't it give error??

and Connor said...

"But i have read that NULL literal can not be used in return_expr of CASE expr"

Where did you read that ?

SQL> select
  2    case when 1=1 then 'Y' end c1,
  3    case when 1=0 then 'Y' end c2,
  4    case when 1=0 then 'Y' else null end c3
  5  from dual;

C C C
- - -
Y

null is fine.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Aditya Mishra, April 29, 2016 - 5:51 am UTC

http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

and it is mentioned that:
"In a simple CASE expression, Oracle Database searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null. You cannot specify the literal NULL for every return_expr and the else_expr.<"

and also the same thing in "Oracle Database 12c SQL Workshop I (Student Guide - Volume 1)" which i get from OCP training from Oracle.
Please correct me if i misunderstood any thing

May 01, 2016 - 10:56 am UTC

Looks like a doc bug

SQL> select
  2        case when 1=1 then null end c1,
  3        case when 1=0 then null end c2,
  4        case when 1=0 then null else null end c3
  5      from dual;

C C C
- - -

read the 12c doc

Laurent Schneider, April 29, 2016 - 1:39 pm UTC

You probably aced the exam. And, while I have no 10g there, the 10g docu is not correct

select case null when null then null else null end from dual

sounds like a valid statement.

I cannot read that non-sense in 12c

http://docs.oracle.com/database/121/SQLRF/expressions004.htm#SQLRF20037

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer