2

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
  • I guess this is for SQL-Server? (edit the question and tag accordingly). Anyway, no, you cannot refer in the WHERE clause to anything that is produced/defined at the SELECT list. – ypercubeᵀᴹ Jul 24 '15 at 19:28
3

From Itzik Ben-Gan's Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference):

Logical Query Processing Order:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
    1. Evaluate Expressions
    2. Remove Duplicates
  6. ORDER BY
  7. OFFSET-FETCH/TOP

Window Functions operate in steps 5.1. and 6.

Obviously then, you cannot access the results of a Window Function in the WHERE or HAVING clauses. You can always use a CTE or a subquery.

See also:
- Logical Processing Order of the SELECT statement (SQL Server 2014 - 2017)
- Logical Query Processing: What It Is And What It Means to You (Itzik Ben Gavin - ITProToday)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.