2

Is it possible to create, inside a single select statement, like this:

SELECT  
   name,  
   CASE  
      WHEN name = 'a' THEN 'a'  
      ELSE 'b'  
   END tag,  
   CASE  
      WHEN tag = 'a' THEN 'a2'  
      ELSE tag = 'b2'  
   END tag2  
FROM table_name

My reason is there is a previous column on the single statement where I want to use its case statement result on my other column.

I want to know if it can be done using variables like:

SELECT  
   name,  
   CASE  
      WHEN name = 'a' THEN 'a'  
      ELSE 'b'
   END @result = tag,  
   CASE  @result
      WHEN 'a' THEN 'a2'  
      ELSE 'b2'  
   END tag2  
FROM table_name

I know the second code is incorrect but that is the logic I need. Thanks guys!

  • 2
    Is this for SQL Server, some other DBMS or a general SQL question? – ypercubeᵀᴹ Jan 29 '17 at 3:15
4

You need to either repeat the expression, or derive it in a separate scope, e.g.:

SELECT name, tag, CASE
   WHEN tag = 'a' THEN 'a2' 
   ELSE 'b2' END AS tag2
FROM
(
   SELECT  name, CASE  
        WHEN name = 'a' THEN 'a'  
        ELSE 'b' END AS tag
   FROM dbo.table_name
) AS derived;
  • can you change the alias = to AS alias? The question is tagged with sql only, – ypercubeᵀᴹ Jan 29 '17 at 3:13
  • @ypercube Was an educated guess based on all of OP's other questions. – Aaron Bertrand Jan 29 '17 at 3:19
  • OK then, I asked the OP on a comment. By the way, MySQL has also variables named @name and maybe other DBMS as well, I don''t know – ypercubeᵀᴹ Jan 29 '17 at 3:22
  • thanks Aaron! I found it useful. BTW, it's for SQL Server. But I think its logically applicable to MyQSL and/or Oracle – Edgar Allan Bayron Jan 30 '17 at 3:44
  • @edgar imho general or multi-platform SQL questions are rarely useful because so few non-trivial problems can have one single universal answer. – Aaron Bertrand Jan 30 '17 at 4:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.