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

[RiddleMeThis]

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'.

SELECT accountNum
 
,balanceCalculation = CASE
    WHEN Balance > 5000 THEN Balance *2
    ELSE Balance /2
END 
 
,balanceDescription = CASE 
    WHEN balanceCalculation > 6000 THEN ‘Large Balance’
    ELSE ’small balance’
END
 
 
FROM tblFinances

[ck9663]

It would be better if you handle it on your front-end app.

Nevertheless, try this:

 
 
SELECT accountNum,
      balanceCalculation = 
         CASE
            WHEN Balance > 5000 THEN Balance * 2
            ELSE Balance / 2
         END,
       balanceDescription =
         case when 
            CASE
               WHEN Balance > 5000 THEN Balance * 2
               ELSE Balance / 2
            END > 6000 then ‘Large Balance’
         else
            'small balance
         end
FROM tblFinances
 
 

Happy Coding!!!

~~ CK

[RiddleMeThis]

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 :)

[ck9663]

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

[JonathanVH]

You could use a common table expression, e.g.:

WITH BalCalc AS
(SELECT AccountNum, CASE WHEN Balance > 5000 THEN Balance * 2 ELSE Balance / 2 END AS BalanceCalculation
 FROM dbo.tblFinance)
SELECT AccountNum, BalanceCalculation,
 CASE WHEN BalanceCalculation > 6000 THEN 'Large Balance' ELSE 'Small Balance' END AS BalanceDescription
FROM BalCalc;

To extend this:

WITH BalCalc AS
(SELECT AccountNum, CASE WHEN Balance > 5000 THEN Balance * 2 ELSE Balance / 2 END AS BalanceCalculation
 FROM dbo.tblFinance),
BalDesc AS
(SELECT AccountNum, BalanceCalculation,
 CASE WHEN BalanceCalculation > 6000 THEN 'Large Balance' ELSE 'Small Balance' END AS BalanceDescription
 FROM BalCalc)
SELECT AccountNum, BalanceCalculation, BalanceDescription
FROM BalDesc;

[RiddleMeThis]

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 :)