Issue using ROWNUM

Question

Post reply

Issue using ROWNUM

jeffshelix

SSCrazy

Points: 2257
More actions
December 18, 2019 at 7:01 pm

#3708877

THi sis just s snippet of the code, i think it is all that is relevant
SELECT * INTO #TEMP_Entry FROM
(SELECT DISTINCT
ROW_NUMBER() OVER (PARTITION BY CONCAT(Z.COMPOSITE_PART,'-',Z.PTNR_SITE_ID) ORDER BY Z.CREATED_DATE DESC) AS RowNum
,T.Part
,T.Supplier
,T.SPI) as Entry

I get Invalid column name 'ROWNUM'. error.
Am I allowed to partition on two columns??

thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jeffshelix SSCrazy Points: 2257 More actions · Answer 1

CLARIFICATION:

I am getting the error on THIS rownum... in the WHERE CLAUSE on the above code;

where Z.SUB_ORG = 'FUDGE'

AND RowNum = 1

and Z.CREATED_DATE > '1/1/2018') AS entry

ScottPletcher SSC Guru Points: 101121 More actions · Answer 2

You don't have a FROM clause in the subquery, so it's not really a valid subquery, even if the "Z" alias is defined somewhere else in the query.

You'd be better off posting the actual query -- even if lengthy -- rather than trying to cut it apart for us. Do use a code box to post it, rather than just as in-line text, if it's long.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

drew.allen SSC Guru Points: 76997 More actions · Answer 3

You should ALWAYS provide the whole query, because the location where the error is detected is often not the location that needs to be fixed.

The logical order of processing is as follows

FROM/JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP/OFFSET

You are trying to use an alias in the WHERE clause (step 2) that is not defined until the SELECT clause (step 5). In addition, your subquery is only referencing one row (the current row being evaluated), so the result is always going to be 1. Since you have not provided your entire query nor have you provided sample data and expected results, we cannot tell you how to rewrite your query to get it to work. My first guess is that you should be using a CTE.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 4

Define the column names of the derived table when specifying the derived table's alias:

SELECT * INTO #TEMP_Entry 
  FROM (SELECT DISTINCT 
          ROW_NUMBER() OVER (PARTITION BY CONCAT(Z.COMPOSITE_PART,'-',Z.PTNR_SITE_ID) 
                                 ORDER BY Z.CREATED_DATE DESC) AS RowNum,
        T.Part, T.Supplier, T.SPI
        ) as Entry(RowNum, Part, Supplier, SPI) -- define column names here
 WHERE RowNum = 1

Eddie Wuerch
MCM: SQL