April 23, 2003 at 3:02 pm
Can someone please explain the behavior of the second and fourth SELECT in the code listed below? I don't understand how the second SELECT works, but since it does, then why doesn't the fourth? Thanks...
Code:
USE Northwind
SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryID DESC
-- Why does this work?
DECLARE @vcValue VARCHAR(255)
SET @vcValue = 'We sell '
SELECT @vcValue = @vcValue + CategoryName + ', ' FROM Categories ORDER BY CategoryID DESC
PRINT @vcValue
SELECT CategoryID INTO #temp FROM Categories WHERE CategoryID > 5
-- Why doesn't this work?
SET @vcValue = 'We sell '
SELECT @vcValue = @vcValue + CategoryName + ', ' FROM Categories a, #temp b WHERE a.CategoryID = b.CategoryID ORDER BY a.CategoryID DESC
PRINT @vcValue
DROP TABLE #temp
Results:
CategoryID CategoryName
----------- ---------------
8 Seafood
7 Produce
6 Meat/Poultry
5 Grains/Cereals
4 Dairy Products
3 Confections
2 Condiments
1 Beverages
(8 row(s) affected)
We sell Seafood, Produce, Meat/Poultry, Grains/Cereals, Dairy Products, Confections, Condiments, Beverages,
(3 row(s) affected)
We sell Meat/Poultry,
April 23, 2003 at 3:32 pm
Oddly this works also:
SET @vcValue = 'We sell '
SELECT @vcValue = @vcValue + a.CategoryName + ', ' FROM Categories a WHERE a.CategoryID IN (SELECT * FROM #temp) ORDER BY a.CategoryID DESC
PRINT @vcValue
It appears important where SQL Server performs the Sort in the execution plan.
April 23, 2003 at 6:11 pm
Which SQL version?
Wondering if you replace the Where with an ON clause
April 23, 2003 at 6:19 pm
SQL Server 7
April 23, 2003 at 10:54 pm
FYI
I Just ran the query (cut and paste from the forum - no changes) on our Server (SQL2K with SP3) and the results are fine.
(3 row(s) affected)
We sell Seafood, Produce, Meat/Poultry,
Greg Norris
DBA to Development team
April 24, 2003 at 12:15 pm
I got this helpful response on another list. Hope it helps others.
---
There is an interesting story behind all this. First read kb287515 @
http://support.microsoft.com/?id=287515
This type of query "can" be used depending on query structure and index(s) on table(s).
To get further insight into problems read the tread: transpose problem @
Pay particular attention to replies by MS's Bill Hollinshead.
RAC v2.1 and QALite released.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply