Xrhrft

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.

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;