Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have a huge query which uses case/when often. Now I have this SQL here, which does not work.

 (select case when xyz.something = 1
     'SOMETEXT'
      (select case when xyz.somethingelse = 1)
          'SOMEOTHERTEXT'
      (select case when xyz.somethingelseagain = 2)
          'SOMEOTHERTEXTGOESHERE'
 end) [ColumnName],
Whats causing trouble is xyz.somethingelseagain = 2, it says it could not bind that expression. xyz is some alias for a table which is joined further down in the query. Whats wrong here? Removing one of the 2 case/whens corrects that, but I need both of them, probably even more cases.
   WHEN xyz.something = 1 THEN 'SOMETEXT'
   WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT'
   WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
   ELSE 'SOMETHING UNKNOWN'
   END AS ColumnName;
                Lets say I have some of the whens as duplicates, like this:  WHEN xyz.something = 1 and xyz.abc = 2 THEN 'SOMETEXT' WHEN xyz.something = 1 and xyz.abc <> 2 THEN 'SOMEOTHERTEXT'  Can I somehow say that if the first when was set that the upcoming ones are skipped? Something which escapes the case/when?
– grady
                Jul 27, 2010 at 10:07
As soon as a WHEN statement is true the break is implicit.
You will have to concider which WHEN Expression is the most likely to happen. If you put that WHEN at the end of a long list of WHEN statements, your sql is likely to be slower. So put it up front as the first.
More information here: break in case statement in T-SQL
                This is true a lot of the time, but is not guaranteed: sqlperformance.com/2014/06/t-sql-queries/…
– RobD
                Aug 22, 2016 at 16:26
    end as col1
-- n=1 => returns SOMETEXT regardless of @m
-- n=2 and m=1 => returns SOMEOTHERTEXT
-- n=2 and m=2 => returns SOMEOTHERTEXTGOESHERE
-- n=2 and m>2 => returns null (no else defined for inner case)
If logical test is against a single column then you could use something like 
USE AdventureWorks2012;  
SELECT   ProductNumber, Category =  
      CASE ProductLine  
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
FROM Production.Product  
ORDER BY ProductNumber;  
More information - https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.