Script function and poor syntax???

  • 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 ON

     

    CitrixApps.CitrixApps = Working.[Applications 1]

                                 UNION

                                 SELECT              [User],

                                                    Location,

                                                    [Application 2]

                                 FROM                 Working

                                 --INNER JOIN    CitrixApps ON

     

    CitrixApps.CitrixApps = Working.[Application 2]

                                 UNION

                                 SELECT              [User],

                                                    Location,

                                                    [Application 3]

                                 FROM                 Working

                                 --INNER JOIN    CitrixApps ON

     

    CitrixApps.CitrixApps = Working.[Application 3]

                                 UNION

                                 SELECT              [User],

                                                    Location,

                                                    [Application 4]

                                 FROM                 Working

                                 --INNER JOIN    CitrixApps ON

     

    CitrixApps.CitrixApps = Working.[Application 4]

                                 UNION

                                 SELECT              [User],

                                                    Location,

                                                    [Application 5]

                                 FROM                 Working

                                 --INNER JOIN    CitrixApps ON

     

    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

     

     

  • 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

  • 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

  • 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