3

I am writing a query, which is using SubQuery to get some result.

Instead of rewriting the SubQuery every time, i want to use the same output value of column 1 as input to another column for the further calculation.

Point 1: Can we use any variable to save the value into it and use the same for another column.

Sample expected code:

  SELECT COLUMN1
        ,CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN 'Cancel' ELSE 'New' END AS **TransactionType**
        ,COLUMN2
        ,CASE **TransactionType** WHEN 'Cancel' THEN EXPR 1.... 
         CASE **TransactionType** WHEN 'New' THEN EXPR 2 .... END AS CALCOLUMN2     
FROM TABLE1
JOIN TABLE2 ....
John Woo
  • 258,903
  • 69
  • 498
  • 492
Arun Singh
  • 1,538
  • 4
  • 19
  • 43

1 Answers1

3

Well, you can't. There are ways to achieve what you want:

A.) using subquery

SELECT Column1, 
       TransactionType,
       CASE TransactionType WHEN 'Cancel' THEN EXPR 1.... 
         CASE TransactionType WHEN 'New' THEN EXPR 2 .... END AS CALCOLUMN2  
FROM
(

   SELECT COLUMN1
        ,CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN   'Cancel' ELSE 'New' END AS **TransactionType**   
   FROM .....
) ...

B.) using the expression itself

SELECT COLUMN1
        ,CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN 'Cancel' ELSE 'New' END AS TransactionType
        ,COLUMN2
        ,CASE (CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN 'Cancel' ELSE 'New' END) WHEN 'Cancel' THEN EXPR 1.... 
         CASE (CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN 'Cancel' ELSE 'New' END) WHEN 'New' THEN EXPR 2 .... END AS CALCOLUMN2     
FROM TABLE1
JOIN TABLE2 ....
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • John, thanks for the quick response. As my query is having many such cases around 20 or more than 20 and i don't want to write the same code from maintainability of code point of view. Looking for an alternate and better approach. – Arun Singh Apr 14 '15 at 11:29