January 26, 2007 at 3:55 am
Can anyone tell me what this script is does - as far as I know it groups users in the DB - and why it's erroring with the syntax?
Thanks,
Select distinct count, derivedtbl2., derivedtbl2.[location], [floor location], [applications 1], [application 2], [application 3], [application 4], [application 5], f15, f16, f17, f18
from working, (SELECT [User], Location,count(*) as count
FROM (
SELECT [User],
Location,
[Applications 1] AS Apps
FROM Working
--INNER JOIN
CitrixApps.CitrixApps = Working.[Applications 1]
SELECT [User],
Location,
[Application 2]
FROM Working
--INNER JOIN
CitrixApps.CitrixApps = Working.[Application 2]
SELECT [User],
Location,
[Application 3]
FROM Working
--INNER JOIN
CitrixApps.CitrixApps = Working.[Application 3]
SELECT [User],
Location,
[Application 4]
FROM Working
--INNER JOIN
CitrixApps.CitrixApps = Working.[Application 4]
SELECT [User],
Location,
[Application 5]
FROM Working
--INNER JOIN
CitrixApps.CitrixApps = Working.[Application 5]
) derivedtbl
where apps not in (select citrixapps from citrixapps)
and apps is not null
GROUP BY [User], Location
having count(*)>=1
--order by 3 desc
) derivedtbl2
where derivedtbl2. = working.
order by count desc
January 26, 2007 at 4:10 am
Jay
From preliminary inspection, it seems to be failing because you've commented out the INNER JOIN clauses but not the predicates (the ON parts).
You should alias your tables - this would make your query easier to read. But don't alias a column as count - this is a reserved word. And, if you have any control over the database design, don't use reserved words such as user, or phrases with spaces in, for the column names of your base tables. Then you can get rid of the distracting square brackets.
John
February 2, 2007 at 2:27 am
Much appreciated John, this is indeed a non-production database I'd pieced together on the quick-quick for urgent reporting purposes, I can alter the column names after checking a list of reserved keywords in BOL.
JB
February 2, 2007 at 2:40 am
JB
You'll know the reserved words because they appear pink or blue in Query Analyzer.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply