We help IT Professionals succeed at work.

Error when using ROW_COUNT alias in where clause

137 Views
Last Modified: 2018-10-15
I have the following statement where I am trying to filter by RN = 1. I am using row_count. However, I keep receiving invalid column RN even though it is a column with the results.
Screenshot attached.

SELECT DISTINCT 
                         dbo.AUDIT_TRAIL.SOURCE_TABLE, dbo.AUDIT_TRAIL.SOURCE_AK, dbo.AUDIT_TRAIL.SOURCE_FIELD, dbo.AUDIT_TRAIL.ORIG_VALUE, 
                         dbo.AUDIT_TRAIL.NEW_VALUE, dbo.BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE AS SHIP_ENTRY_DATE, dbo.AUDIT_TRAIL.STAMPTIME, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, dbo.SYS_USERS.USER_NAME, dbo.SYS_USERS.FIRST_NAME, dbo.SYS_USERS.LAST_NAME, 
                         dbo.VIEW_SM_STOCK.STM_AUTO_KEY, dbo.VIEW_SM_STOCK.QTY_RESERVED, dbo.VIEW_SM_STOCK.QTY_SHIPPED, dbo.VIEW_SM_STOCK.STOCK_LINE, 
                         dbo.VIEW_SM_STOCK.CONDITION_CODE, dbo.VIEW_SM_STOCK.WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.ORIG_WAREHOUSE_CODE, 
                         dbo.VIEW_SM_STOCK.LOCATION_CODE, dbo.VIEW_SM_STOCK.SERIAL_NUMBER, dbo.SYS_USERS.EMPLOYEE_CODE,
						 ROW_NUMBER() OVER(PARTITION BY BA_VIEW_SHIPPING_ORDERS.SM_NUMBER,AUDIT_TRAIL.SOURCE_AK ORDER BY VIEW_SM_STOCK.STM_AUTO_KEY DESC) AS RN
FROM            dbo.VIEW_SM_STOCK RIGHT OUTER JOIN
                         dbo.BA_VIEW_SHIPPING_ORDERS ON dbo.VIEW_SM_STOCK.SMD_AUTO_KEY = dbo.BA_VIEW_SHIPPING_ORDERS.SMD_AUTO_KEY LEFT OUTER JOIN
                         dbo.AUDIT_TRAIL LEFT OUTER JOIN
                         dbo.SYS_USERS ON dbo.AUDIT_TRAIL.SYSUR_AUTO_KEY = dbo.SYS_USERS.SYSUR_AUTO_KEY ON 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SMH_AUTO_KEY = dbo.AUDIT_TRAIL.SOURCE_AK						 
WHERE        (dbo.AUDIT_TRAIL.SOURCE_TABLE = 'SMH') AND (dbo.AUDIT_TRAIL.ORIG_VALUE = 'PICKING') AND (dbo.AUDIT_TRAIL.NEW_VALUE = 'PACKING') OR RN = '1' 
GROUP BY dbo.AUDIT_TRAIL.SOURCE_AK, dbo.VIEW_SM_STOCK.STM_AUTO_KEY, dbo.AUDIT_TRAIL.SOURCE_TABLE, dbo.AUDIT_TRAIL.SOURCE_FIELD, dbo.AUDIT_TRAIL.ORIG_VALUE, 
                         dbo.AUDIT_TRAIL.NEW_VALUE, dbo.BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE, dbo.AUDIT_TRAIL.STAMPTIME, dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, 
                         dbo.SYS_USERS.USER_NAME, dbo.SYS_USERS.FIRST_NAME, dbo.SYS_USERS.LAST_NAME, 
                         dbo.VIEW_SM_STOCK.QTY_RESERVED, dbo.VIEW_SM_STOCK.QTY_SHIPPED, dbo.VIEW_SM_STOCK.STOCK_LINE, dbo.VIEW_SM_STOCK.CONDITION_CODE, 
                         dbo.VIEW_SM_STOCK.WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.ORIG_WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.LOCATION_CODE, 
                         dbo.VIEW_SM_STOCK.SERIAL_NUMBER, dbo.SYS_USERS.EMPLOYEE_CODE
ORDER BY SOURCE_AK

Open in new window

SCREENSHOT
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Eyeballeth thy SQL Query Order of Execution.   The reason you are getting that error is because the query engine processes the WHERE clause before the SELECT clause, so that the time of WHERE processing it has no idea what RN is because it hasn't been defined yet.

This can be confusing at first as the visual order of a query, SELECT - FROM with JOINs - WHERE - GROUP BY - ORDER BY - HAVING, is different from how the query engine processes it.

A common way around this is to throw your whole query minus the WHERE RN = 1 part into a subquery, then in a 'wrapper' main query SELECT all the columns from your subquery, and there you can use the WHERE RN = 1 as it will exist.

i.e.

SELECT a.{all columns}
FROM ( 
   -- your query here, minus the WHERE RN = 1 part ) a
WHERE a.RN = 1

Open in new window

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
I would be trying something like (a CTE query) :

;with CTE as
(
  SELECT -- DISTINCT 
                         dbo.AUDIT_TRAIL.SOURCE_TABLE, dbo.AUDIT_TRAIL.SOURCE_AK, dbo.AUDIT_TRAIL.SOURCE_FIELD, dbo.AUDIT_TRAIL.ORIG_VALUE, 
                         dbo.AUDIT_TRAIL.NEW_VALUE, dbo.BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE AS SHIP_ENTRY_DATE, dbo.AUDIT_TRAIL.STAMPTIME, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, dbo.SYS_USERS.USER_NAME, dbo.SYS_USERS.FIRST_NAME, dbo.SYS_USERS.LAST_NAME, 
                         dbo.VIEW_SM_STOCK.STM_AUTO_KEY, dbo.VIEW_SM_STOCK.QTY_RESERVED, dbo.VIEW_SM_STOCK.QTY_SHIPPED, dbo.VIEW_SM_STOCK.STOCK_LINE, 
                         dbo.VIEW_SM_STOCK.CONDITION_CODE, dbo.VIEW_SM_STOCK.WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.ORIG_WAREHOUSE_CODE, 
                         dbo.VIEW_SM_STOCK.LOCATION_CODE, dbo.VIEW_SM_STOCK.SERIAL_NUMBER, dbo.SYS_USERS.EMPLOYEE_CODE,
                         ROW_NUMBER() OVER(PARTITION BY BA_VIEW_SHIPPING_ORDERS.SM_NUMBER,AUDIT_TRAIL.SOURCE_AK ORDER BY VIEW_SM_STOCK.STM_AUTO_KEY DESC) AS RN
   FROM            dbo.VIEW_SM_STOCK RIGHT OUTER JOIN
                         dbo.BA_VIEW_SHIPPING_ORDERS ON dbo.VIEW_SM_STOCK.SMD_AUTO_KEY = dbo.BA_VIEW_SHIPPING_ORDERS.SMD_AUTO_KEY LEFT OUTER JOIN
                         dbo.AUDIT_TRAIL LEFT OUTER JOIN
                         dbo.SYS_USERS ON dbo.AUDIT_TRAIL.SYSUR_AUTO_KEY = dbo.SYS_USERS.SYSUR_AUTO_KEY ON 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SMH_AUTO_KEY = dbo.AUDIT_TRAIL.SOURCE_AK						 
-- WHERE        (dbo.AUDIT_TRAIL.SOURCE_TABLE = 'SMH') AND (dbo.AUDIT_TRAIL.ORIG_VALUE = 'PICKING') AND (dbo.AUDIT_TRAIL.NEW_VALUE = 'PACKING') OR RN = '1' 
   GROUP BY dbo.AUDIT_TRAIL.SOURCE_AK, dbo.VIEW_SM_STOCK.STM_AUTO_KEY, dbo.AUDIT_TRAIL.SOURCE_TABLE, dbo.AUDIT_TRAIL.SOURCE_FIELD, dbo.AUDIT_TRAIL.ORIG_VALUE, 
                         dbo.AUDIT_TRAIL.NEW_VALUE, dbo.BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE, dbo.AUDIT_TRAIL.STAMPTIME, dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, 
                         dbo.SYS_USERS.USER_NAME, dbo.SYS_USERS.FIRST_NAME, dbo.SYS_USERS.LAST_NAME, 
                         dbo.VIEW_SM_STOCK.QTY_RESERVED, dbo.VIEW_SM_STOCK.QTY_SHIPPED, dbo.VIEW_SM_STOCK.STOCK_LINE, dbo.VIEW_SM_STOCK.CONDITION_CODE, 
                         dbo.VIEW_SM_STOCK.WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.ORIG_WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.LOCATION_CODE, 
                         dbo.VIEW_SM_STOCK.SERIAL_NUMBER, dbo.SYS_USERS.EMPLOYEE_CODE
-- ORDER BY SOURCE_AK   -- moved to final select, otherwise have to use TOP in the initial select
)
  SELECT DISTINCT *
  FROM cte 
  WHERE ((SOURCE_TABLE = 'SMH') AND (ORIG_VALUE = 'PICKING') AND (NEW_VALUE = 'PACKING')) OR RN = '1'
  ORDER BY SOURCE_AK

Open in new window

Be careful as to using OR in the where clause, make very sure you use brackets to force the correct conditions. I have made an assumption above for that 'OR' in the where clause.

Basic rule of thumb is you need to GROUP BY for aggregations. Use DISTINCT for unique rows. You dont need both together....

Using a CTE (Common Table Expression) is a way of giving a name to a query, in this case, I named it (unimaginatively) "CTE". Then you can subsequently select from the name you gave it -
 a bit like a subquery / derived table (except we start by giving it a name).. Extremely useful when generating new columns as part of a query.

Does that make sense ?
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
LOL "you say pot-a-to I say po-ta-toh"

If you define a derived table conventionally, or via a CTE it is the effectively the same
select * 
from (
  some subquery here
          ) as D                << here is the declared alias

- v -

with D as (                     << here is the declared alias
   some subquery here
   )           
select * 
from D                       

Open in new window

Whilst not directly relevant to this question common tables expressions are needed for recursion, but optional otherwise.

It is a worthwhile point made regarding the OR in the where clause, using indentation can also help to make this clear:
SELECT DISTINCT
    *
FROM (
    SELECT
...
) d
WHERE (d.SOURCE_TABLE = 'SMH'
    AND d.ORIG_VALUE = 'PICKING'
    AND d.NEW_VALUE = 'PACKING'
      )
OR d.RN = '1'
ORDER BY
    d.SOURCE_AK

Open in new window

However we can only guess if this is required in your where clause or not.
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thank you all for your help, greatly apprecaited. How can I place the statement using CTE in a view for a user to access?
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
To create a view :
CREATE VIEW VW_AUDIT_TRAIL as
with CTE as
(
  SELECT -- DISTINCT 
                         dbo.AUDIT_TRAIL.SOURCE_TABLE, dbo.AUDIT_TRAIL.SOURCE_AK, dbo.AUDIT_TRAIL.SOURCE_FIELD, dbo.AUDIT_TRAIL.ORIG_VALUE, 
                         dbo.AUDIT_TRAIL.NEW_VALUE, dbo.BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE AS SHIP_ENTRY_DATE, dbo.AUDIT_TRAIL.STAMPTIME, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, dbo.SYS_USERS.USER_NAME, dbo.SYS_USERS.FIRST_NAME, dbo.SYS_USERS.LAST_NAME, 
                         dbo.VIEW_SM_STOCK.STM_AUTO_KEY, dbo.VIEW_SM_STOCK.QTY_RESERVED, dbo.VIEW_SM_STOCK.QTY_SHIPPED, dbo.VIEW_SM_STOCK.STOCK_LINE, 
                         dbo.VIEW_SM_STOCK.CONDITION_CODE, dbo.VIEW_SM_STOCK.WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.ORIG_WAREHOUSE_CODE, 
                         dbo.VIEW_SM_STOCK.LOCATION_CODE, dbo.VIEW_SM_STOCK.SERIAL_NUMBER, dbo.SYS_USERS.EMPLOYEE_CODE,
                         ROW_NUMBER() OVER(PARTITION BY BA_VIEW_SHIPPING_ORDERS.SM_NUMBER,AUDIT_TRAIL.SOURCE_AK ORDER BY VIEW_SM_STOCK.STM_AUTO_KEY DESC) AS RN
   FROM            dbo.VIEW_SM_STOCK RIGHT OUTER JOIN
                         dbo.BA_VIEW_SHIPPING_ORDERS ON dbo.VIEW_SM_STOCK.SMD_AUTO_KEY = dbo.BA_VIEW_SHIPPING_ORDERS.SMD_AUTO_KEY LEFT OUTER JOIN
                         dbo.AUDIT_TRAIL LEFT OUTER JOIN
                         dbo.SYS_USERS ON dbo.AUDIT_TRAIL.SYSUR_AUTO_KEY = dbo.SYS_USERS.SYSUR_AUTO_KEY ON 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SMH_AUTO_KEY = dbo.AUDIT_TRAIL.SOURCE_AK						 
   WHERE        dbo.AUDIT_TRAIL.SOURCE_TABLE = 'SMH' AND dbo.AUDIT_TRAIL.ORIG_VALUE = 'PICKING' AND dbo.AUDIT_TRAIL.NEW_VALUE = 'PACKING' 
   GROUP BY dbo.AUDIT_TRAIL.SOURCE_AK, dbo.VIEW_SM_STOCK.STM_AUTO_KEY, dbo.AUDIT_TRAIL.SOURCE_TABLE, dbo.AUDIT_TRAIL.SOURCE_FIELD, dbo.AUDIT_TRAIL.ORIG_VALUE, 
                         dbo.AUDIT_TRAIL.NEW_VALUE, dbo.BA_VIEW_SHIPPING_ORDERS.ENTRY_DATE, dbo.AUDIT_TRAIL.STAMPTIME, dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, 
                         dbo.SYS_USERS.USER_NAME, dbo.SYS_USERS.FIRST_NAME, dbo.SYS_USERS.LAST_NAME, 
                         dbo.VIEW_SM_STOCK.QTY_RESERVED, dbo.VIEW_SM_STOCK.QTY_SHIPPED, dbo.VIEW_SM_STOCK.STOCK_LINE, dbo.VIEW_SM_STOCK.CONDITION_CODE, 
                         dbo.VIEW_SM_STOCK.WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.ORIG_WAREHOUSE_CODE, dbo.VIEW_SM_STOCK.LOCATION_CODE, 
                         dbo.VIEW_SM_STOCK.SERIAL_NUMBER, dbo.SYS_USERS.EMPLOYEE_CODE
-- ORDER BY SOURCE_AK   -- moved to final select, otherwise have to use TOP in the initial select
)
  SELECT *  -- really should use column names, not select * , and if wanting to keep ORDER BY then make you select : select top 100 percent
  FROM cte 
  WHERE RN = 1  -- maybe comment this out so you can use RN< 3 or RN = 1 etc....
--  ORDER BY SOURCE_AK   -- order by is invalid in a view unless you use TOP in the sellect clause e.g. select top 100 percent 
GO

-- then to test

select * from VW_AUDIT_TRAIL
order by source_ak

Open in new window

And please note the embedded comments in the code snippet above.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
You do NOT have to use a cte for this query. There is NO technical advantage here.

What is important is that row_number is part of a subquery, then you may filter by that calculation. The subquery may be in the traditional form or it can be a cte. Both do exactly the same thing in this query.