-4

I am trying to write a query. Column A comes from database, column B is either 1 or -1 and it is result of a CASE statement that looks into the same table. Now, my column C needs to refer to column B as part of it's calculation (let's say it is Column_B * Column_X ). How is this done since I cannot use Column_B in my SELECT statement.

2 Answers 2

6

If I read your question correctly, what I think you are asking is - how do I use the result of one calculated column in another? Well, if you want to actually add that column to the table you could just inline the calculation within your new column - this would work but requires more maintenance and is probably not an ideal solution (this also works in a SELECT statement but can increase maintenance overhead):

CREATE TABLE T
( 
    A INT NOT NULL
  , X INT NOT NULL
  , (CASE A WHEN NULL THEN 0 ELSE 1 END) AS B
  , (CASE A WHEN NULL THEN 0 ELSE 1 END) * X AS C
);

This can become messy and can be painful to maintain if the calculations are complicated. You can however define your base calculations on the table and then put a view over the top which can then use your calculated columns:

CREATE VIEW V
AS
SELECT A, B, B * X AS C
  FROM T;

This is my recommendation due to how really simple it is to maintain because the logic is isolated. The calculation of C never needs to know how B is calculated, it just uses the value that is generated. If you don't like that, then your other option is to define the logic within the query against the table itself and then perform the calculation on that. There are a few ways to do this, here are a couple of the ones I prefer:

COMMON TABLE EXPRESSIONS

-- CTE Method
WITH results AS
(
    SELECT A
         , (CASE A WHEN NULL THEN 0 ELSE 1 END) AS B
         , X -- don't forget to include the other columns you need!
       FROM MyTable
)
SELECT A, B, B * X AS C
   FROM results;

SUBQUERIES

-- SubQuery
SELECT A, B, B * X AS C
  FROM (SELECT A
             , (CASE A WHEN NULL THEN 0 ELSE 1 END) AS B
             , X -- don't forget to include the other columns you need!
          FROM MyTable) AS results;

TABLE VARIABLE

-- Total overkill, but can be done (could also use a temp table instead)
DECLARE @results TABLE
(
    A INT NOT NULL,
    B INT NOT NULL,
    X INT NOT NULL
);

INSERT @results
SELECT A
    , (CASE A WHEN NULL THEN 0 ELSE 1 END) AS B
    , X -- don't forget to include the other columns you need!
  FROM MyTable;

SELECT A, B, B * X AS C
  FROM @results;
2
  • what is the best approach if I have multiple calculated columns. A scenario when I have A and X from table and then: B: calculated from A and X. C:calculated from A and B, and D: calculated from C and X, etc.
    – Nico M.
    Apr 8, 2019 at 23:29
  • Use the CTE approach, you can have multiple CTE's which then derive values from the previous ones. This will allow you to have calculations that rely on multiple other calculations. Apr 8, 2019 at 23:38
0

You can use OUTER APPLY to contain the calculated fields, which also makes it easy to use calculations in group by or order by

Select A, B, B*X C
from Yourtable y
outer apply (select case .... end B) calcs
order by B

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.