By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

A CASE statement using another CASE statement as it's expression

P: 8
Im trying to use 2 CASE statements together in a SELECT query without much luck.
The second CASE uses the output from the first CASE as it’s expression (well it’s supposed to).
When the 2nd CASE statement is removed it works, but when it is included i get this error:
Invalid column name 'balanceCalculation'.
Expand|Select|Wrap|Line Numbers
  1. SELECT accountNum
  2.  
  3. ,balanceCalculation = CASE
  4.     WHEN Balance > 5000 THEN Balance *2
  5.     ELSE Balance /2
  6. END 
  7.  
  8. ,balanceDescription = CASE 
  9.     WHEN balanceCalculation > 6000 THEN ‘Large Balance’
  10.     ELSE ’small balance’
  11. END
  12.  
  13.  
  14. FROM tblFinances
Feb 4 '10 #1

✓ answered by JonathanVH

You could use a common table expression, e.g.:
Expand|Select|Wrap|Line Numbers
  1. WITH BalCalc AS
  2. (SELECT AccountNum, CASE WHEN Balance > 5000 THEN Balance * 2 ELSE Balance / 2 END AS BalanceCalculation
  3.  FROM dbo.tblFinance)
  4. SELECT AccountNum, BalanceCalculation,
  5.  CASE WHEN BalanceCalculation > 6000 THEN 'Large Balance' ELSE 'Small Balance' END AS BalanceDescription
  6. FROM BalCalc;
To extend this:
Expand|Select|Wrap|Line Numbers
  1. WITH BalCalc AS
  2. (SELECT AccountNum, CASE WHEN Balance > 5000 THEN Balance * 2 ELSE Balance / 2 END AS BalanceCalculation
  3.  FROM dbo.tblFinance),
  4. BalDesc AS
  5. (SELECT AccountNum, BalanceCalculation,
  6.  CASE WHEN BalanceCalculation > 6000 THEN 'Large Balance' ELSE 'Small Balance' END AS BalanceDescription
  7.  FROM BalCalc)
  8. SELECT AccountNum, BalanceCalculation, BalanceDescription
  9. FROM BalDesc;

Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
It would be better if you handle it on your front-end app.

Nevertheless, try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. SELECT accountNum,
  4.       balanceCalculation = 
  5.          CASE
  6.             WHEN Balance > 5000 THEN Balance * 2
  7.             ELSE Balance / 2
  8.          END,
  9.        balanceDescription =
  10.          case when 
  11.             CASE
  12.                WHEN Balance > 5000 THEN Balance * 2
  13.                ELSE Balance / 2
  14.             END > 6000 then ‘Large Balance’
  15.          else
  16.             'small balance
  17.          end
  18. FROM tblFinances
  19.  
  20.  
Happy Coding!!!

~~ CK
Feb 4 '10 #2

P: 8
Hi, thanks for that ck, it works great :)

To take it one step further though (my example was very simple and stripped down), is it possible to store the output of the first case statement in a variable, then use that variable in other case statements later in the code? Its just that I have quite long cases, and they get used by other cases several times, so to repeat them inside other cases over and over is going to make the query massive.

If it's not possible then nevermind, the way you showed me works anyway so I can use that if nothing else, it would just be good to make things smaller instead of repeating the same code too many times.
Thanks again :)
Feb 5 '10 #3

ck9663
Expert 2.5K+
P: 2,878
If you're thinking of using a variable inside the query and use that same variable in the same query, the answer is no. You can either use subquery or a scalar function instead.

Good Luck!!!


~~ CK
Feb 5 '10 #4

P: 2
You could use a common table expression, e.g.:
Expand|Select|Wrap|Line Numbers
  1. WITH BalCalc AS
  2. (SELECT AccountNum, CASE WHEN Balance > 5000 THEN Balance * 2 ELSE Balance / 2 END AS BalanceCalculation
  3.  FROM dbo.tblFinance)
  4. SELECT AccountNum, BalanceCalculation,
  5.  CASE WHEN BalanceCalculation > 6000 THEN 'Large Balance' ELSE 'Small Balance' END AS BalanceDescription
  6. FROM BalCalc;
To extend this:
Expand|Select|Wrap|Line Numbers
  1. WITH BalCalc AS
  2. (SELECT AccountNum, CASE WHEN Balance > 5000 THEN Balance * 2 ELSE Balance / 2 END AS BalanceCalculation
  3.  FROM dbo.tblFinance),
  4. BalDesc AS
  5. (SELECT AccountNum, BalanceCalculation,
  6.  CASE WHEN BalanceCalculation > 6000 THEN 'Large Balance' ELSE 'Small Balance' END AS BalanceDescription
  7.  FROM BalCalc)
  8. SELECT AccountNum, BalanceCalculation, BalanceDescription
  9. FROM BalDesc;
Feb 6 '10 #5

P: 8
Thats great, thanks very much to both of you, the info you provided has saved me a lot of time and given me other things to consider and research, much appreciated :)
Feb 8 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.