SQL-server syntax error when combining queries (pass-through query)

Question

I'm trying to combine 2 queries to a sql-server pass-through query (PTQ). Both queries, when coded separately in Access, work. When combining these Access codes to one query, it still works in Access. But when I format the field names to T-sql, and try to run as a PTQ, I get a syntax error near "GROUP". When I delete this "GROUP BY" line, I get a syntax error near ";".

SELECT jaar, 
       maand, 
       wicode, 
       pg_intern_code, 
       pg_kriss_code, 
       pg_kriss_naam, 
       Count(pc0517.dbo.resul.klavnr) AS AantalVanKLAVNR 
FROM   (SELECT Year(pc0517.dbo.resul.created)  AS JAAR, 
               Month(pc0517.dbo.resul.created) AS MAAND, 
               pc0517.dbo.resul.wicode, 
               dbo.tblproductgroep_intern.pg_intern_code, 
               dbo.tblproductgroep_kriss.pg_kriss_code, 
               dbo.tblproductgroep_kriss.pg_kriss_naam, 
               pc0517.dbo.resul.klavnr 
        FROM   (dbo.tblproductgroep_kriss 
                INNER JOIN (dbo.tblproductgroep_intern 
                            INNER JOIN dbo.tblrobbe_pg 
                                    ON dbo.tblproductgroep_intern.pg_intern_id = 
                                       dbo.tblrobbe_pg.pg_code_intern_id) 
                        ON dbo.tblproductgroep_kriss.pg_kriss_id = 
                           dbo.tblrobbe_pg.pg_code_kriss_id) 
               INNER JOIN pc0517.dbo.resul 
                       ON dbo.tblrobbe_pg.robbe_pg_naam = 
                          pc0517.dbo.resul.prcode 
        WHERE  ( ( Year(pc0517.dbo.resul.created) = 2012 ) 
                 AND ( pc0517.dbo.resul.kanaal = "gg" ) )) 
GROUP  BY jaar, 
          maand, 
          wicode, 
          pg_intern_code, 
          pg_kriss_code, 
          pg_kriss_naam;

Other PTQ's on the same database (even combined with the PC0517 database), work without any glitch, but I'm totally new to SQL-server, so I don't know all the conventions yet, and am probably overlooking something basic...

TIA

Thanks to whoever changed the formatting :) And the nasty stuff might also be due to the fact that I have to combine an awful lot of tables (not my database)... — user1453540, Jun 13 '12 at 12:13

Aaron Bertrand · Answer 1 · 2012-06-13T12:26:37.973

I corrected a variety of things: (1) put the joins in the right order (2) removed misplaced parentheses (3) added table aliases for readability (4) removed double quotes around "gg" (5) made the query against r.created an open-ended range so that an index could be used if it exists. I also removed jaar from the inner query in the grouping - based on the where clause, this can never be anything other than 2012.

I don't know what Access will do with this if you paste it back into there, but you should be able to confirm this is a more correct query than the garbage Access produced for you by running it against SQL Server directly. If Access still breaks this, I would put it in a stored procedure or a view in SQL Server, and point Access at that.

SELECT 
   jaar = 2012, 
   maand, 
   wicode, 
   pg_intern_code, 
   pg_kriss_code, 
   pg_kriss_naam, 
   COUNT(klavnr) AS AantalVanKLAVNR 
FROM   
(
    SELECT 
       maand = MONTH(r.created), 
       r.wicode, 
       i.pg_intern_code, 
       k.pg_kriss_code, 
       k.pg_kriss_naam, 
       r.klavnr 
    FROM 
       dbo.tblproductgroep_kriss AS k
    INNER JOIN dbo.tblrobbe_pg AS tr 
       ON k.pg_kriss_id = tr.pg_code_kriss_id
    INNER JOIN dbo.tblproductgroep_intern AS i 
       ON i.pg_intern_id = tr.pg_code_intern_id
    INNER JOIN pc0517.dbo.resul AS r 
       ON tr.robbe_pg_naam = r.prcode 
    WHERE 
       r.created >= '20120101' AND r.created < '20130101'
       AND r.kanaal = 'gg'
) AS x 
GROUP BY 
   maand, 
   wicode, 
   pg_intern_code, 
   pg_kriss_code, 
   pg_kriss_naam;

It can also be simplified as:

    SELECT 
       jaar = 2012,
       maand = MONTH(r.created), 
       r.wicode, 
       i.pg_intern_code, 
       k.pg_kriss_code, 
       k.pg_kriss_naam, 
       r.klavnr 
    FROM 
       dbo.tblproductgroep_kriss AS k
    INNER JOIN dbo.tblrobbe_pg AS tr 
       ON k.pg_kriss_id = tr.pg_code_kriss_id
    INNER JOIN dbo.tblproductgroep_intern AS i 
       ON i.pg_intern_id = tr.pg_code_intern_id
    INNER JOIN pc0517.dbo.resul AS r 
       ON tr.robbe_pg_naam = r.prcode 
    WHERE 
       r.created >= '20120101' AND r.created < '20130101'
       AND r.kanaal = 'gg'
    GROUP BY 
       MONTH(r.created), 
       r.wicode, 
       i.pg_intern_code, 
       k.pg_kriss_code, 
       k.pg_kriss_naam;

(In other words, the subquery seems unnecessary.)

This works like a charm. Thank you so much for taking the time! — user1453540, Jun 13 '12 at 12:18

SQL-server syntax error when combining queries (pass-through query)

1 Answers1