MYSQL Case Statements

Hi Experts

Is it possible to define variable within a case statement?

Example

If I had three case statements that worked out different calculations. Could I give each case statement a variable which is equal to the value found, which I could call globally to do other calculations.

Example
========
Case statement 1

ROUND(CASE
WHEN theme_keywords.CompetingPages   < 50000                                                                                                                                                                    THEN  theme_keywords.CompetingPages/4000
WHEN theme_keywords.CompetingPages   > 50001                   AND  theme_keywords.CompetingPages   <= 1100000       THEN  theme_keywords.CompetingPages/15000
WHEN theme_keywords.CompetingPages   > 1100001             AND  theme_keywords.CompetingPages   <= 10000000       THEN  theme_keywords.CompetingPages/12000
WHEN theme_keywords.CompetingPages   > 10000001       AND  theme_keywords.CompetingPages   <= 20000000       THEN  theme_keywords.CompetingPages/9000
WHEN theme_keywords.CompetingPages   > 20000001       AND  theme_keywords.CompetingPages   <= 30000000       THEN  theme_keywords.CompetingPages/8500
WHEN theme_keywords.CompetingPages   > 30000001       AND  theme_keywords.CompetingPages   <= 40000000       THEN  theme_keywords.CompetingPages/8000
WHEN theme_keywords.CompetingPages   > 40000001       AND  theme_keywords.CompetingPages   <= 213000000 THEN  theme_keywords.CompetingPages/7800      
WHEN theme_keywords.CompetingPages   > 213000000                                                                                                                                                            THEN  theme_keywords.CompetingPages/7500      
ELSE 0
END,0) AS SBL,

If I had case statement 2

and

case statement 3

and wanted to take

case statement 1 + case statement 2 + case statement 3 and add them all together to get a value as an example how would I do this?

Is this possible?

Thanks
matthewdacruzAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have 2 options:
* copy the 3 case statements for that expression ( case  ... end + case ... end + case .. end )
* use inline view:
select res1 , res2, res3 , res1 + res2 + res3 from (select case .. end res1, case .. end res2 , case ... end res3 from ... where .. ) 

Open in new window


note that you might consider to create a lookup table with the ranges and the / value ... to simplify the code, and make it more flexible (aka configurable)
0
 
matthewdacruzAuthor Commented:
Is if possible to use the @variable in the case staement?
0
 
matthewdacruzAuthor Commented:
How would you create a lookup table to work with this?
0
 
matthewdacruzAuthor Commented:
Thanks your suggestion worked well
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.