Help with a query that compares

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

General SQL Server Forums

New to SQL Server Programming

Help with a query that compares

Author

Topic

Lorrec
Starting Member

1 Post

Posted - 2009-10-05 : 10:19:35

Hello. I work with SMS 2003 and SQL 2005. I created a query that displayed the resource id and installed date for two packages (package a and package b). I needed the query to output the resource id if package b's installed date was older than package a. I received some help with the query but I get an error message.

I am fairly new to SQL and can do basic queries but this one is causing me problems. Any help would be appreciated.

Query

select main.resourceID
from
(SELECT ResourceID, max(InstallDate0) as InstallDate0
case when max(installDate0) >
FROM v_GS_CCP_Packages0
WHERE AppName0 in ( 'WindowsXPSP3'))
Group by ResourceID) main inner join
(SELECT ResourceID, max(InstallDate0) as InstallDate0
case when max(installDate0) >
FROM v_GS_CCP_Packages0
WHERE AppName0 in ( 'WindowsXPSP3_CleanUp')) sub on sub.resourceID = main.ResourceID
where sub.installdate0 > main.installdate0

Error

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'case'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'case'.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-05 : 10:41:53

Start by formatting your code

And this is a mess



    SELECT main.resourceID
      FROM (
				SELECT ResourceID
					 , MAX(InstallDate0) AS InstallDate0
					 , CASE WHEN MAX(installDate0) > /* n THEN x ELSE y END AS something */
				  FROM v_GS_CCP_Packages0
				 WHERE AppName0 in ('WindowsXPSP3') /* )  -- What's this for?  And why IN??? */
			  GROUP BY ResourceID
		   ) AS main
 
INNER JOIN
		   (
				SELECT ResourceID
					 , MAX(InstallDate0) AS InstallDate0
					 , CASE WHEN MAX(installDate0) > /* n THEN x ELSE y END AS something */
				  FROM v_GS_CCP_Packages0
				 WHERE AppName0 in ( 'WindowsXPSP3_CleanUp')
			) AS sub 
		ON sub.resourceID = main.ResourceID
     WHERE sub.installdate0 > main.installdate0

Read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources