Skip to Main Content

Breadcrumb

Warning

Before you submit your comment...

Remember - this is about feedback to the original question. AskTOM is about building a strong Oracle community, not stealing time away from others needing assistance by sneaking in a brand new question under the guise of "Comment".

If your comment requires a response that might include a query, please supply very simple CREATE TABLE and INSERT statements. Nice simple scripts so we can replicate your issue simply.

Remember to take a look at Legal Notices and Terms of Use before supplying a comment...

Don't forget, your comments are public. If you want to send a comment to just the AskTOM team, please use the feedback option

Comment

Highlight any SQL, PL/SQL, or fixed-width text and click the <code> button
 (will never be published or used to spam you)

Question and Answer

Connor McDonald

Thanks for the question, Aditya.

Asked: April 28, 2016 - 6:17 am UTC

Last updated: May 01, 2016 - 10:56 am UTC

Version: 11g

Viewed 1000+ times

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