CODEWITHSUNDEEP
sql-server
Jackal
Jackal

Reputation: 3521

Sql Server how to add computed columns to variable and use it in another column

Hi I have the following query

GO
DECLARE @tempTable TABLE
(
    Id INT PRIMARY KEY,
    Referencia VARCHAR(15),
    UAP NVARCHAR(20),
    ConsumoWeek01 FLOAT,
    ConsumoWeek02 FLOAT,
    Stock INT,
    PecasPorCaixa INT
    UNIQUE (Id)
)

INSERT INTO
    @tempTable
SELECT *
FROM
    viewConsumoPDP

SELECT 
    C.Id,
    C.Referencia,
    C.UAP,
    C.ConsumoWeek01 AS ConsumoInicialWeek01,
    T.ConsumoWeek01 AS ConsumoActualWeek01,
    T.ConsumoWeek01 / 5 AS ConsumoDiarioWeek01,
    T.ConsumoWeek02 AS ConsumoWeek02
FROM 
    @tempTable T
INNER JOIN Parametros P
ON P.Referencia = T.Referencia 
AND P.UAP = T.UAP
INNER JOIN Consumos C
ON C.Referencia = P.Referencia
AND C.UAP = P.UAP

I need to create some variables that are calculated using some columns values and I need to use that variable to calculate other columns.

Reason why is because I don't want to do all calculations on the select statement and it will be a big confusion but I have no idea if it is possible using SQL Transact.

For example

GO
    DECLARE @tempTable TABLE
    (
        Id INT PRIMARY KEY,
        Referencia VARCHAR(15),
        UAP NVARCHAR(20),
        ConsumoWeek01 FLOAT,
        ConsumoWeek02 FLOAT,
        Stock INT,
        PecasPorCaixa INT
        UNIQUE (Id)
    )

    DECLARE @NumPAB INT

    INSERT INTO
        @tempTable
    SELECT *
    FROM
        viewConsumoPDP

    SELECT 
        C.Id,
        C.Referencia,
        C.UAP,
        C.ConsumoWeek01 AS ConsumoInicialWeek01,
        T.ConsumoWeek01 AS ConsumoActualWeek01,
        T.ConsumoWeek01 / 5 AS ConsumoDiarioWeek01,
        T.ConsumoWeek02 AS ConsumoWeek02,
        @ConsumoPAB = P.NumPab / T.ConsumoWeek01                 
    FROM 
        @tempTable T
    INNER JOIN Parametros P
    ON P.Referencia = T.Referencia 
    AND P.UAP = T.UAP
    INNER JOIN Consumos C
    ON C.Referencia = P.Referencia
    AND C.UAP = P.UAP

You can see i'd like to store the result of the calculation in a variable and later use it in another computed column. But how to achieve this if i cannot assign the value within the select statement?

Upvotes: 0

Views: 2497

Answers (1)

Luis Cazares
Luis Cazares

Reputation: 3585

With a SELECT statement, you can return a result set OR assign values to variables. You can't do both operations at the same time.

The main reason is that both operations are logically different. One will handle a set of values, while the other will handle scalar values.

Upvotes: 3

Related Questions