CODEWITHSUNDEEP
sqloracle-databasecase
Nico Robin
Nico Robin

Reputation: 91

Case in Select Statement issue?

I would normally use the popular if-else statement in java, but in sqlplus I'm using the case in select statement to query the conditional statement that I have here below.

 select title, to_char(nvl2(((retail-cost)/cost)*100, 
        ((retail-cost)/cost)*100, 
        ((retail-cost)/cost)*100), '99999.99') "Margin",
        to_char(discount, '99999.99') "Discount",
 (case when ("Margin" >= 60) then 'Very High Profit'
       when ("Margin" >= 30) then 'High Profit'
       else ("Margin" >= 0)  then 'Loss Leader' 
       end) "Pricing Structure"
    from books
    order by title;

I was hoping to get something like this as my result, but I tried to move the ordering; I'm still stuck with an error every time.

TITLE                          Margin   Discount  Pricing Structure
------------------------------ -------- --------- ---------------------------------
BIG BEAR AND LITTLE DOVE          68.23           Very high profit
BODYBUILD IN 10 MINUTES A DAY     65.07           Very high profit

Upvotes: 1

Views: 125

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

Use a Common Table Expression (CTE) to factor out the calculation from the case logic:

WITH CDATA AS (select title,
                      ((retail-cost)/cost)*100 AS MARGIN,
                      to_char(discount, '99999.99') AS "Discount"
                 from books)
SELECT TITLE,
       TO_CHAR(MARGIN, '99999.99') AS "Margin",
       "Discount",
       case
         when MARGIN >= 60 then 'Very High Profit'
         when MARGIN >= 30 then 'High Profit'
         else MARGIN >= 0  then 'Loss Leader' 
       end AS "Pricing Structure"
  order by title;

Best of luck.

Upvotes: 0

Jucan
Jucan

Reputation: 421

sql can't see the alias unless it's in a subquery. You should write it something like:

case 
when (retail-cost/cost)*100 >= 60 then 'Very High Profit'
when (retail-cost/cost)*100 >= 30 then 'High Profit'
when  (retail-cost/cost)*100 >= 0  then 'Loss Leader' 
else 'SOMETHING HERE'
end "Pricing Structure"

Something else to consider is that this nvl2:

to_char(nvl2(((retail-cost)/cost)*100, 
    ((retail-cost)/cost)*100, 
    ((retail-cost)/cost)*100), '99999.99')

does nothing for you. Why? cause nvl2(exp1,exp2,exp3). If exp1 is not null then print exp2, when it is null then print exp3. Not only that but your NVL does nothing here cause it will always output ((retail-cost)/cost)*100. You're better off just writing to_char(((retail-cost)/cost)*100),'99999.99').

If your exp1 = exp2 then you're better off just writing NVL(exp1,exp2). If exp1 is not null then it will print it, otherwise it will print exp2.

Upvotes: 4

Noha Elprince
Noha Elprince

Reputation: 1934

You cannot use the alias "Margin" in your case statement. You may use the whole formula of "Margin" in your case statement like:

(case (the NVL statement of Margin) > 60)

Also, make sure to match the same data types in your case statement. So, You cannot use to_char( ) > 60 since you are comparing a character to integer.

Hope this may help :-)

Upvotes: 0

Related Questions