August 12, 2010 at 5:39 am
Using the following in a Stored Proc the WHERE clause says that RANK is not defined, why? Note: The ORDER BY clause has no problems with RANK.
INSERT INTO @Products
SELECT Row_Number() OVER ( ORDER BY idProduct ) as RowNumber, idProduct, Description,
SUBSTRING(Details, 1, @DescriptionLength) + '...' AS Details,
ListPrice, Price, smallImageUrl, imageUrl, sku,
3 * dbo.WordCount(@Word1, Description) + dbo.WordCount(@Word1, Details) +
3 * dbo.WordCount(@Word2, Description) + dbo.WordCount(@Word2, Details) +
3 * dbo.WordCount(@Word3, Description) + dbo.WordCount(@Word3, Details) +
3 * dbo.WordCount(@Word4, Description) + dbo.WordCount(@Word4, Details) +
3 * dbo.WordCount(@Word5, Description) + dbo.WordCount(@Word5, Details)
AS [RANK]
FROM [dbo].CPNET_Product
WHERE RANK > 0
ORDER BY [RANK] DESC
August 12, 2010 at 5:49 am
Am i right in saying that you cannot use a name that you give a calculation in a select statement within the where clause.
Therefore RANK is not recognised, but if you type the calculation again within the where clause (no need to specificy 'As RANK') it should work.
I also think that the reason that the Order By clause doesnt fall over is because SQL server never got to it as it fell over on the Where clause before.
Correct me if im wrong, but hope this helps.
August 12, 2010 at 5:59 am
If you see the Execution Plan for the query, the picture will be clear. The WHERE Clause is actually evaluated before your SELECT Clause and the ORDER BY Clause. Hence you get the error.
ORDER BY Clause gets evaluated after the SELECT Clause and hence you don't get any error if you use it.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 12, 2010 at 6:07 am
If you remove the WHERE clause the ORDER BY works just fine.
I'll try your suggestion.
August 12, 2010 at 6:09 am
Hi Kingston,
Thanks for the reply. I believe that makes perfect sense.
As a teachable moment - how can I see the "Execution Plan"
August 12, 2010 at 6:13 am
That worked! Thanks.
August 12, 2010 at 6:29 am
To see the execution plan, you can select the option Query -> Include Actual Execution Plan. Now if you run the SELECT query you will see the Execution Plan after the Results and Messages Tab.
You don't have to actually remove the the Where Clause to make the query work. Just put the SELECT query in a sub query and then apply the Where Clause, it will work.
INSERT INTO @Products
SELECT *
FROM (
SELECT Row_Number() OVER ( ORDER BY idProduct ) as RowNumber, idProduct, Description,
SUBSTRING(Details, 1, @DescriptionLength) + '...' AS Details,
ListPrice, Price, smallImageUrl, imageUrl, sku,
3 * dbo.WordCount(@Word1, Description) + dbo.WordCount(@Word1, Details) +
3 * dbo.WordCount(@Word2, Description) + dbo.WordCount(@Word2, Details) +
3 * dbo.WordCount(@Word3, Description) + dbo.WordCount(@Word3, Details) +
3 * dbo.WordCount(@Word4, Description) + dbo.WordCount(@Word4, Details) +
3 * dbo.WordCount(@Word5, Description) + dbo.WordCount(@Word5, Details)
AS [RANK]
FROM [dbo].CPNET_Product
) CPNET_Product
WHERE RANK > 0
ORDER BY [RANK] DESC
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 12, 2010 at 8:14 am
Thanks, most helpful and the suggestion to include it in a sub-query makes the maintenance much easier.
Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply