how do I do a nested case in mysql?

Question

observe the following line used in a select statement

(case when p1 = 0 then 1 else (p2-p1)/p1 end) as delta_pct,

this line gives the percentage change between p1 and p2, and if p1 is 0 it returns 1 to avoid the divide by 0 error. but it gives 1 if p2 is also 0 which is incorrect. how do I modify his line to account for that case as well?

something like

case when p1 = 0
    case when p2 = 0
        then 0
    then 1
else (p2-p1)/p1

Does this answer your question? [Best way to do nested case statement logic in SQL Server](https://stackoverflow.com/questions/505747/best-way-to-do-nested-case-statement-logic-in-sql-server) — Ashu, Mar 01 '21 at 08:44

score 1 · Answer 1 · answered Mar 01 '21 at 08:46

1

You could use a CASE expression with more levels to cover all edge cases:

CASE WHEN p1 = 0 AND p2 = 0 THEN 0
     WHEN p1 = 0 THEN 1
     ELSE (p2 - p1) / p1 END AS percentage

answered Mar 01 '21 at 08:46

Tim Biegeleisen

502,043
27
286
360

score 0 · Answer 2 · answered Mar 01 '21 at 09:08

normally instead of nested case you can use case expressione with several AND condition for cover your logic but i you really need nested case

but i you really need nested case

case 
when p1 = 0 THEN 
    case when p2 = 0
         then 0
         ELSE 1 
         END 
else (p2-p1)/p1
END delta_pct

as you can cleary see in this case the use of composite and condition is more easy

CASE WHEN p1 = 0 AND p2 = 0 THEN 0
    WHEN p1 = 0 THEN 1
 ELSE (p2 - p1) / p1 END delta_pct

how do I do a nested case in mysql?

2 Answers2