Escribiría mi consulta de esta manera pero

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)

Pero el servidor sql no puede ver el 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'. 

¡Debo repetir la fórmula y la consulta no será fácil de leer!

,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
1
Evilripper 2 mar. 2018 a las 14:22

4 respuestas

La mejor respuesta

Básicamente lo que dijo A.van Esveld:

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
2
Radu Gheorghiu 2 mar. 2018 a las 11:40

Lo que tiendo a hacer cuando realmente quiero usar el alias para mantenerlo legible es ponerlo en una subconsulta, de esa manera puede usar el alias en la selección.

2
A. van Esveld 2 mar. 2018 a las 11:27

Debe repetir la fórmula, el analizador de consultas se encarga de ello.

SELECT (Field1 - Field2) QtyDiff,
       IIF((Field1 - Field2) > 0, (Field1 - Field2), 0) QtyDiffPositive
1
McNets 2 mar. 2018 a las 11:30

Intenta usar subconsulta.

    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
1
Przemek Filipczyk 2 mar. 2018 a las 11:30