• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

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
0
matthewdacruz
Asked:
matthewdacruz
  • 3
1 Solution
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now