Fustrating SELECT-in-FROM clause Issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Fustrating SELECT-in-FROM clause Issue

Author

Topic

digital3
Starting Member

7 Posts

Posted - 2005-08-23 : 19:27:35

I have two tables. One records hits on Objects by Users (#Hit). The other keeps a summary of totals on a (ObjectID,UserID) basis (#HitCount). These tables are usually kept precisely in sync, but a maintenence script was asked for "just in case" corrections need to be made to keep the tables consistent.

The point of this SQL is to find out if there are any (ObjectID, UserID) combinations in #Hit that do not have a summary row in #HitCount. In this demo code, I've made sure there is. Combination(1000,2) should have a summary row with a count of 2, but doesn't, and this SELECT is supposed to catch that.

The tricky bit is the SELECT also needs to retrieve the date/time of the two most recent hits for each (ObjectID,UserID). The exact reason for needing to retrieve these two dates isn't so important, suffice to say the dates are needed for a specific JOIN (not shown). I couldn't think of any other way to achieve this except to use a Sub-SELECT as a column inside a larger SELECT.

What I can't figure out is why one of the two SELECT statements below fails to execute. I've highlighted the problematic part with a red comment. It has something to do with the fact one uses a direct table reference in the FROM clause, and the other does not. I was under the impression that it made no difference whether the FROM clause referred to a table, or was the result of a subselect.

The code below should go if you dump it in the Analyzer.

Can anyone help me here on why the ObjectID and UserID columns cannot be seen from the inner SELECT?

Cheers

SET NOCOUNT ON
CREATE TABLE #Hit(UserID int, ObjectID int, [DateTime] datetime)

INSERT INTO #Hit VALUES(1,1000,'1-Jan-2000')
INSERT INTO #Hit VALUES(1,1000,'1-Jan-1999')
INSERT INTO #Hit VALUES(2,1000,'1-Sep-2000')
INSERT INTO #Hit VALUES(2,1000,'1-Sep-1999')

CREATE TABLE #HitCount(UserID int, ObjectID int, HitCount int)
INSERT INTO #HitCount VALUES(1,1000,2)

GO
--Version 1: This fails
SELECT  ObjectUserHitCount.ObjectID, ObjectUserHitCount.UserID,

	--Get the most recent hit date/time for this (Object,User) combination
	(SELECT TOP 1 #Hit.DateTime 
         FROM #Hit 
         WHERE ObjectUserHitCount.ObjectID = #Hit.ObjectID 
               AND ObjectUserHitCount.UserID = #Hit.UserID 
         ORDER BY #Hit.DateTime DESC) AS LastHit,

	--Get the 2nd most recent hit date/time for this 
        --(Object,User) combination using a (TOP 1 Asc (TOP 2 Desc)) method 
	--Seems the nested (SELECT TOP 2..) command cannot 
        --see the ObjectUserHitCount.ObjectID and UserID columns... why?
	(SELECT TOP 1 TempTable.DateTime 
         FROM (  SELECT TOP 2 #Hit.DateTime FROM #Hit 
                 WHERE ObjectUserHitCount.ObjectID = #Hit.ObjectID 
                       AND ObjectUserHitCount.UserID = #Hit.UserID 
                 ORDER BY #Hit.DateTime DESC
              ) TempTable 
         ORDER BY TempTable.DateTime ASC) AS SecondLastHit

	
	--Gets a list of (ObjectID, UserID) combinations from #Hits 
        --that do NOT have a summary row in #HitCount
FROM 	(SELECT UniqueObjectUserCombinations.ObjectID, UniqueObjectUserCombinations.UserID
	 FROM 	(SELECT DISTINCT #Hit.ObjectID, #Hit.UserID 
                 FROM #Hit) UniqueObjectUserCombinations 

                LEFT OUTER JOIN #HitCount 
                     ON #HitCount.ObjectID = UniqueObjectUserCombinations.ObjectID 
                        AND #HitCount.UserID = UniqueObjectUserCombinations.UserID

	 WHERE	#HitCount.ObjectID IS NULL) ObjectUserHitCount 

GO




/*
--Version 2: This works (as in it executes), but won't return the results I want. 
--It's just a simplified version I'm using to try and diagnose what's wrong
--with Version 1.
--The only difference from Version 1 is the FROM clause refers to a 
--table instead of a subSELECT. 
SELECT  ObjectUserHitCount.ObjectID, ObjectUserHitCount.UserID,

	--Get the most recent hit date/time for this (Object,User) combination
	(SELECT TOP 1 #Hit.DateTime 
         FROM #Hit 
         WHERE ObjectUserHitCount.ObjectID = #Hit.ObjectID 
               AND ObjectUserHitCount.UserID = #Hit.UserID 
         ORDER BY #Hit.DateTime DESC) AS LastHit,

	--Get the 2nd most recent hit date/time for this 
        --(Object,User) combination using a (TOP 1 Asc (TOP 2 Desc)) method 	
        --The nested (SELECT TOP 2..) command CAN
        --see the ObjectUserHitCount.ObjectID and UserID columns.	
        (SELECT TOP 1 TempTable.DateTime 
         FROM (  SELECT TOP 2 #Hit.DateTime FROM #Hit 
                 WHERE ObjectUserHitCount.ObjectID = #Hit.ObjectID 
                       AND ObjectUserHitCount.UserID = #Hit.UserID 
                 ORDER BY #Hit.DateTime DESC
              ) TempTable 
         ORDER BY TempTable.DateTime ASC) AS SecondLastHit
	
FROM 	#HitCount ObjectUserHitCount 

GO */



DROP TABLE #Hit
DROP TABLE #HitCount
SET NOCOUNT OFF
GO

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-24 : 08:42:44

So yeah... in a given select you cannot use table references that are out of scope. for example a double select cannot reference the root recordsets...

Try this out:


Select
	*,
		(SELECT TOP 1 #Hit.DateTime 
		FROM #Hit 
	         WHERE #Hit.ObjectID = Wahoo.ObjectID 
	               AND #Hit.UserID = Wahoo.UserID
			AND #Hit.DateTime < Wahoo.LastHit
	         ORDER BY #Hit.DateTime DESC) AS SecondLastHit
From
	(
	SELECT  
		ObjectUserHitCount.ObjectID, 
		ObjectUserHitCount.UserID,
	
		--Get the most recent hit date/time for this (Object,User) combination
		(SELECT TOP 1 #Hit.DateTime 
		FROM #Hit 
	         WHERE #Hit.ObjectID = ObjectUserHitCount.ObjectID 
	               AND #Hit.UserID = ObjectUserHitCount.UserID
	         ORDER BY #Hit.DateTime DESC) AS LastHit
	
		--Get the 2nd most recent hit date/time for this 
	        --(Object,User) combination using a (TOP 1 Asc (TOP 2 Desc)) method 
		--Seems the nested (SELECT TOP 2..) command cannot 
	        --see the ObjectUserHitCount.ObjectID and UserID columns... why?
/*		(SELECT TOP 1 TempTable.DateTime 
	         FROM (  SELECT TOP 2 #Hit.DateTime FROM #Hit 
	                 WHERE ObjectUserHitCount.ObjectID = #Hit.ObjectID 
	                       AND ObjectUserHitCount.UserID = #Hit.UserID 
	                 ORDER BY #Hit.DateTime DESC
	              ) TempTable 
	         ORDER BY TempTable.DateTime ASC) AS SecondLastHit
*/	
		
		--Gets a list of (ObjectID, UserID) combinations from #Hits 
	        --that do NOT have a summary row in #HitCount
	FROM 	(SELECT UniqueObjectUserCombinations.ObjectID, UniqueObjectUserCombinations.UserID
		 FROM 	(SELECT DISTINCT #Hit.ObjectID, #Hit.UserID 
	                 FROM #Hit) UniqueObjectUserCombinations 
	
	                LEFT OUTER JOIN #HitCount 
	                     ON #HitCount.ObjectID = UniqueObjectUserCombinations.ObjectID 
	                        AND #HitCount.UserID = UniqueObjectUserCombinations.UserID
	
		 WHERE	#HitCount.ObjectID IS NULL) ObjectUserHitCount 
	) Wahoo

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

digital3
Starting Member

7 Posts

Posted - 2005-08-24 : 19:33:00

Awesome.

Cheers for your help on that one Corey. It works sweet.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources