SQL Use Value from different column on same Select

I have a question if this is possible which will save me time writing extra code and limits user error. I need to use a value from a column (which has already performed some calulcation) from the same select then do extra calculation on it.

I encounter this a lot in my job. I will highlight the problem with a small example.

I have the following table created with one row added to it:

DECLARE @info AS TABLE
(
    Name VARCHAR(500),
    Value_A NUMERIC(8, 2) 
)

INSERT INTO @info
VALUES ('Test Name 1', 10.20)

Now the requirements is to produce a select with 2 columns. First column needs to multiple Value_A by 10 and then the second column needs to add 1 to the first column. Below is the full requirements added:

SELECT  (I.Value_A * 10) ,
        (I.Value_A * 10) + 1
FROM    @info AS I

As you can see, I just copied and pasted the first column code to second column and added one to it. Is there a way I can just reference the first column and just add + 1 instead of the copy and paste?

I can achieve this in a another way using an insert block followed by an update block. I can create a temp table, insert the first column to it then update second column. However, this means I have wrote extra code. I am looking for a solution which I only need to use one select.

Above is a small example. Normally, the problems I face is bigger select with more calculation or logic.

Answer

You can move the expression to the FROM clause using APPLY:

SELECT v.col1, v.col1 + 1
FROM @info I CROSS APPLY
     (VALUES (I.Value_A * 10)) v(col1);

Advertisement

Answer