logic problem with dynamic variables

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 2005 Forums

Transact-SQL (2005)

logic problem with dynamic variables

Author

Topic

McRed
Starting Member

8 Posts

Posted - 2009-04-03 : 15:47:55

I'm having a problem getting the results I want. The problem I'm having is that in the code below '61' is static. However, what I need to happen is that in the #Results table the CategoryID needs to be passed to the @ValueToUpdate, so that for every row in the table CategoryID will have its row counted in another table. I'm having a hard time explaining because obviously I'm not quite clear now how to accomplish this task.


DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate  = '01/01/2009' 
SET @EndDate  = '02/01/2009'

DECLARE @cat varchar(10)
SET @cat = '61'

CREATE TABLE #Results (CompanyName varchar(100), CompanyID int, CategoryID int, PageName varchar(100), ViewCount varchar(10))

INSERT INTO #Results (CompanyName, CompanyID, CategoryID, PageName)
	SELECT Manufacturer.CompanyName, MFGPlacement.CompanyID, MFGPlacement.CategoryID, MFGCategoryShow.CategoryName FROM Manufacturer
	INNER JOIN  MFGPlacement
	ON Manufacturer.CompanyID = MFGPlacement.CompanyID and Manufacturer.Paid = '1'
	INNER JOIN MFGCategoryShow
	ON MFGCategoryShow.CategoryID = MFGPlacement.CategoryID 

DECLARE @ValueToUpdate varchar(10)
	SET @ValueToUpdate = (Select COUNT(*) FROM SiteAccess WHERE URLTO like '%mfg_detail.aspx?CatID=' + @cat and DateAccessed >= @StartDate and DateAccessed < @EndDate)
	UPDATE #Results
	SET ViewCount = @ValueToUpdate WHERE CategoryID = @cat

SELECT * FROM #Results ORDER BY CompanyName ASC

Drop TABLE #Results

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 16:17:25

See if this works:


INSERT INTO #Results (CompanyName, CompanyID, CategoryID, PageName, Viewcount)
SELECT Manufacturer.CompanyName
       ,MFGPlacement.CompanyID
       ,MFGPlacement.CategoryID
       ,MFGCategoryShow.CategoryName 
       ,oa.viewCount
FROM   Manufacturer
INNER JOIN  MFGPlacement
       ON Manufacturer.CompanyID = MFGPlacement.CompanyID and Manufacturer.Paid = '1'
INNER JOIN MFGCategoryShow
       ON MFGCategoryShow.CategoryID = MFGPlacement.CategoryID 
outer apply
       (
       Select COUNT(*) as ViewCount
       FROM SiteAccess 
       WHERE URLTO like '%mfg_detail.aspx?CatID=' + convert(varchar(10), MFGPlacement.CategoryID)
       and    DateAccessed >= @StartDate 
       and    DateAccessed < @EndDate
       ) oa

Be One with the Optimizer
TG

McRed
Starting Member

8 Posts

Posted - 2009-04-06 : 08:23:14

I've never used OUTER APPLY before and when I copied and pasted your code I get incorrect syntax near the keyword 'outer'. I do notice that in SQL Server Management Express that the word 'apply' is black unlike outer or join which are grayed out.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-06 : 08:44:56

What is your compatibility level set to?

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 08:51:51

Run this to know it

EXEC sp_helpdb 'your_dbname'

Madhivanan

Failing to plan is Planning to fail

McRed
Starting Member

8 Posts

Posted - 2009-04-06 : 09:33:13

Compatibility Level is set to 80

Here is the rest in case you need info from it also.

Status=ONLINE,
Updateability=READ_WRITE,
UserAccess=MULTI_USER,
Recovery=FULL,
Version=539,
Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52,
IsTornPageDetectionEnabled,
IsAutoCreateStatistics,
IsAutoUpdateStatistics

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-06 : 10:14:25

Look at the database properties, see if you have an option to set the compatibility level to 90. If the database was restored from an eariler version then that would explain why the compatibility level was 80.

Be One with the Optimizer
TG

McRed
Starting Member

8 Posts

Posted - 2009-04-06 : 10:19:05

I tried to change the level to 90 and got this message:
Valid values of database compatibility level are 60, 65, 70, or 80.

So I immediately contacted the hosting company and asked them to set the level to 90. They will get back to me later in the day. Will follow up here, lets hope they don't give me the run around.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-06 : 10:34:37

>>Valid values of database compatibility level are 60, 65, 70, or 80.
This would imply that the server is not version 2005 but still at 2000. What version your hosting company uses if up to you guys. We can re-write the query not to use functionality from 2005. Perhaps this will work:


INSERT INTO #Results (CompanyName, CompanyID, CategoryID, PageName, Viewcount)
SELECT Manufacturer.CompanyName
       ,MFGPlacement.CompanyID
       ,MFGPlacement.CategoryID
       ,MFGCategoryShow.CategoryName 
       ,sa.viewCount
FROM   Manufacturer
INNER JOIN  MFGPlacement
       ON Manufacturer.CompanyID = MFGPlacement.CompanyID and Manufacturer.Paid = '1'
INNER JOIN MFGCategoryShow
       ON MFGCategoryShow.CategoryID = MFGPlacement.CategoryID 
LEFT OUTER JOIN
	(select	URLTO
		,count(*) as Viewcount
	from	SiteAccess
	where	DateAccessed >= @StartDate 
	and     DateAccessed < @EndDate
	group by URLTO
	) sa
		on sa.urlto like '%mfg_detail.aspx?CatID=' + convert(varchar(10), MFGPlacement.CategoryID)

Be One with the Optimizer
TG

McRed
Starting Member

8 Posts

Posted - 2009-04-06 : 10:38:57

Well that worked like a charm. Thanks so much for you help. And you are right. The server is 2000. I've always been working under the assumption that the server was 2005, as that is what I was told when I took over management of this database. lol. In the future I guess I'll be posting these questions in the 2000 forum. :)

d

McRed
Starting Member

8 Posts

Posted - 2009-04-06 : 10:46:27

One final question. When this query runs, it groups by URLTO, which will work, but then it has multiple counts for domain.com and www.domain.com, how would I alter this query to group by MFGPlacement.CategoryID instead?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-06 : 11:12:20

Assuming you don't have categoryid in SiteAccess then you'd have to JOIN siteAccess to MFGPlacement in the subquery then you have all the columns from both tables available to include in the group by clause. Don't forget to change the ON clause to reflect the appropriate correlation for whatever you group by.

Be One with the Optimizer
TG

McRed
Starting Member

8 Posts

Posted - 2009-04-06 : 11:55:55

OK I did something wrong, when I try to execute, it never finishes and I have to cancel the query after a few minutes. Is this heading in the right direction?


LEFT OUTER JOIN
	(select MFGPlacement.CategoryID, SiteAccess.URLTO,count(*) as Viewcount
	from MFGPlacement
	INNER JOIN SiteAccess
	ON DateAccessed >= @StartDate and DateAccessed < @EndDate
	group by MFGPlacement.CategoryID, SiteAccess.URLTO
	) sa
		on sa.urlto like '%mfg_detail.aspx?CatID=' + convert(varchar(10), MFGPlacement.CategoryID)

McRed
Starting Member

8 Posts

Posted - 2009-04-06 : 15:57:49

Wow, I got it now. I was missing the easy solution. This query inserts all the data I need into a temp table, so I made a group by when I select the information out of that table for viewing. Much easier than what I was trying to do.

Thanks for all the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources