I've successfully added a ROW_NUMBER()
function to my SELECT
statement, but I get an "invalid column name" error when I try to reference the column I generated with the ROW_NUMBER()
function in my WHERE
clause by referencing the [RANK]
alias.
Query
SELECT DISTINCT
ROW_NUMBER () OVER (PARTITION BY T0.DocNum ORDER BY T0.DocNum) [RANK],
T0.DocNum,
T3.[PickIdNo],
T0.[NumAtCard],
T0.[U_STS_SalesProg],
T0.[DocDate],
T0.[DocDueDate],
T0.[TaxDate],
T0.[CardCode],
T0.[CardName],
T6.[SlpName],
T8.[firstName][CSS],
T4.[U_BOTYPE],
T1.LineTotal BackOrderTotal,
T2. OrderTotal
FROM ORDR T0
INNER JOIN
(SELECT
T0.DocEntry
, ROUND(SUM(T0.[OpenQty]*T0.[Price]),2) LineTotal
FROM RDR1 T0 where LineStatus ='O'
GROUP BY T0.DocEntry) T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN
(SELECT
T0.DocEntry
, ROUND(SUM(T0.[Quantity]*T0.[Price]),2) OrderTotal
FROM RDR1 T0
GROUP BY T0.DocEntry) T2 ON T0.DocEntry = T2.DocEntry
INNER JOIN RDR1 T3 ON T0.[DocEntry] = T3.[DocEntry]
INNER JOIN OCRD T4 ON T0.[CardCode] = T4.[CardCode]
INNER JOIN OTER T5 ON T4.[Territory] = T5.[territryID]
INNER JOIN OSLP T6 ON T4.[SlpCode] = T6.[SlpCode]
INNER JOIN OITM T7 ON T3.[ItemCode] = T7.[ItemCode]
INNER JOIN OHEM T8 ON T4.[DfTcnician] = T8.[empID]
INNER JOIN OITW T9 ON T7.[ItemCode] = T9.[ItemCode]
WHERE Rank = '1' -- <<== This is where it fails
ORDER BY T8.[firstName], T0.[CardName], t0.DocNum
WHERE
clause to anything that is produced/defined at theSELECT
list. – ypercubeᵀᴹ Jul 24 '15 at 19:28