Problem with ROW_NUMBER() OVER... and a calculated row

Question

Post reply

Problem with ROW_NUMBER() OVER... and a calculated row

boeledi

Ten Centuries

Points: 1151
More actions
September 22, 2010 at 9:00 am

#240817

Dear All,
Who could tell me how to solve this issue?
Please find here below an extract of a stored procedure:
WITH Paging AS (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY Score DESC)
, va.advertiser_id
, va.section_id
, Score = (CASE WHEN AD.dataInt_2 < 2 THEN 1 ELSE 0 END
+ CASE WHEN AD.dataOpt_1 = 1 THEN 1 ELSE 0 END)
FROM
view_active_advertisers va
INNER JOIN tbl_advertiser_category AC
ON va.advertiser_id = AC.advertiser_id AND va.section_id = AC.section_id
INNER JOIN tbl_advertiser_section_data AD
ON va.advertiser_id = AD.advertiser_id AND va.section_id = AD.section_id AND va.category_name = AD.category_name
WHERE
va.section_id = @in_section_id
AND
va.category_name = @in_category_name
AND
AD.dataInt_1 < 3
AND
va.openSunday = 1
AND
AC.category_name = @in_sub_category
)
When I try to "save" it, SQL Server says "Invalid column name: 'Score'".
Why?
Many thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

You might not be having field name called Score in any of the following tables

view_active_advertisers

tbl_advertiser_category

tbl_advertiser_section_data

Thanks
Parthi

LutzM SSC Guru Points: 107049 More actions · Answer 2

The problem is you're using a column alias that is unknown to the ROW_NUMBER() function the way the query is written.

Two options:

a) replace the Score alias with the original case statement:

rn = ROW_NUMBER() OVER (ORDER BY (CASE WHEN AD.dataInt_2 < 2 THEN 1 ELSE 0 END

+ CASE WHEN AD.dataOpt_1 = 1 THEN 1 ELSE 0 END) DESC)

b) use a subquery or CTE to assign the alias and use ROW_UMBER in your outer SELECT:

WITH Paging AS (

SELECT

, va.advertiser_id

, va.section_id

, Score = (CASE WHEN AD.dataInt_2 < 2 THEN 1 ELSE 0 END

+ CASE WHEN AD.dataOpt_1 = 1 THEN 1 ELSE 0 END)

FROM

view_active_advertisers va

INNER JOIN tbl_advertiser_category AC

ON va.advertiser_id = AC.advertiser_id AND va.section_id = AC.section_id

INNER JOIN tbl_advertiser_section_data AD

ON va.advertiser_id = AD.advertiser_id AND va.section_id = AD.section_id AND va.category_name = AD.category_name

WHERE

va.section_id = @in_section_id

AND

va.category_name = @in_category_name

AND

AD.dataInt_1 < 3

AND

va.openSunday = 1

AND

AC.category_name = @in_sub_category

), numbered as

(

SELECT *,

rn = ROW_NUMBER() OVER (ORDER BY Score DESC)

FROM Paging

)

SELECT * FROM numbered

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]