CODEWITHSUNDEEP
sqlsql-serversql-server-2017
Evilripper
Evilripper

Reputation: 1185

SQL Server how to use alias in the same query?

I would write my query in this way but

SELECT 
     TempInventoryComparisonReal.Quantity - ISNULL([TempInventoryComparisonVirtual].[Quantity], 0)  QuantityDifference
     ,IIF((QuantityDifference > 0), QuantityDifference, 0) QuantityDifferencePositive
FROM [TempInventoryComparisonReal]
LEFT JOIN [TempInventoryComparisonVirtual] 
ON ([TempInventoryComparisonReal].ItemsCoresId = [TempInventoryComparisonVirtual].ItemsCoresId 
AND [TempInventoryComparisonReal].Dimensions1Id=[TempInventoryComparisonVirtual].Dimensions1Id
AND [TempInventoryComparisonReal].Dimensions2Id=[TempInventoryComparisonVirtual].Dimensions2Id
AND [TempInventoryComparisonReal].SerialNumber=[TempInventoryComparisonVirtual].SerialNumber)

but sql server can't see the alias 

Msg 207, Level 16, State 1, Procedure InventoryComparisonRealView, Line 5 [Batch Start Line 37]
Invalid column name 'QuantityDifference'.
Msg 207, Level 16, State 1, Procedure InventoryComparisonRealView, Line 5 [Batch Start Line 37]
Invalid column name 'QuantityDifference'.

I must repeat the formula and the query become not easy to read!

,ISNULL([TempInventoryComparisonVirtual].[Quantity], 0) QuantityVirtual
,IIF((ISNULL([TempInventoryComparisonVirtual].[Quantity], 0) - TempInventoryComparisonReal.Quantity)>0, (ISNULL([TempInventoryComparisonVirtual].[Quantity], 0) - TempInventoryComparisonReal.Quantity), 0) QuantityDifferencePositive
,IIF((ISNULL([TempInventoryComparisonVirtual].[Quantity], 0) - TempInventoryComparisonReal.Quantity)<0,(ISNULL([TempInventoryComparisonVirtual].[Quantity], 0) - TempInventoryComparisonReal.Quantity) , 0) QuantityDifferenceNegative

Upvotes: 1

Views: 75

Answers (4)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

Basically what A.van Esveld said:

SELECT QuantityDifference
    , IIF((QuantityDifference > 0), QuantityDifference, 0) QuantityDifferencePositive
FROM (
    select TempInventoryComparisonReal.Quantity - ISNULL([TempInventoryComparisonVirtual].[Quantity], 0) QuantityDifference
    from [TempInventoryComparisonReal]
    left join [TempInventoryComparisonVirtual] on (
            [TempInventoryComparisonReal].ItemsCoresId = [TempInventoryComparisonVirtual].ItemsCoresId
            and [TempInventoryComparisonReal].Dimensions1Id = [TempInventoryComparisonVirtual].Dimensions1Id
            and [TempInventoryComparisonReal].Dimensions2Id = [TempInventoryComparisonVirtual].Dimensions2Id
            and [TempInventoryComparisonReal].SerialNumber = [TempInventoryComparisonVirtual].SerialNumber
            )
) R

Upvotes: 2

McNets
McNets

Reputation: 10827

You must repeat the formula, query analyzer take care of it.

SELECT (Field1 - Field2) QtyDiff,
       IIF((Field1 - Field2) > 0, (Field1 - Field2), 0) QtyDiffPositive

Upvotes: 1

Przemek Filipczyk
Przemek Filipczyk

Reputation: 68

Try to use subquery.

    SELECT  IIF((QuantityDifference > 0), QuantityDifference, 0) QuantityDifferencePositive 
FROM    (           
            SELECT 
                     TempInventoryComparisonReal.Quantity - ISNULL([TempInventoryComparisonVirtual].[Quantity], 0)  QuantityDifference     
                FROM [TempInventoryComparisonReal]
                LEFT JOIN [TempInventoryComparisonVirtual] 
                ON ([TempInventoryComparisonReal].ItemsCoresId = [TempInventoryComparisonVirtual].ItemsCoresId 
                AND [TempInventoryComparisonReal].Dimensions1Id=[TempInventoryComparisonVirtual].Dimensions1Id
                AND [TempInventoryComparisonReal].Dimensions2Id=[TempInventoryComparisonVirtual].Dimensions2Id
                AND [TempInventoryComparisonReal].SerialNumber=[TempInventoryComparisonVirtual].SerialNumber)
        ) AS X

Upvotes: 1

A. van Esveld
A. van Esveld

Reputation: 258

What I tend to do when I really want to use the alias to keep it readable is put it into a subquery, that way you can use the alias in the select.

Upvotes: 2

Related Questions