Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

sql - Syntax error (missing operator) in query expression

I have the following SQL Statement that works fine in SQL Server but in Access gets me an error

'ERM_HAZARD.[HAZARDID] = ERM_XREF_HAZARD_CAT.[HAZARDID] 
INNER JOIN ERM_HAZARDSTATUS 
ON ERM_HAZARD.HAZARDSTATUSID = ERM_HAZARDSTATUS.HAZARDSTATUSI'

 SELECT erm_hazard.hazardid    AS PKTrans,
       erm_hazard.extref      AS No,
       erm_cat.name           AS CaseType,
       erm_hazard.created     AS CaseDate,
       erm_hazard.name        AS Title,
       erm_hazard.description AS DescriptionTrans,
       erm_organisation.name  AS ResponsibleUnit,
       erm_hazard.reviewdate  AS CaseDeadline,
       erm_hazardstatus.name  AS Status,
       erm_organisation.name  AS UnitInCharge,
       erm_personel.name      AS PersonInCharge
FROM   (erm_personel
        INNER JOIN (erm_organisation
                    INNER JOIN erm_hazard
                            ON erm_organisation.[organisationid] =
                               erm_hazard.[organisationid])
                ON erm_personel.[personelid] = erm_hazard.[personelid])
       INNER JOIN (erm_cat
                   INNER JOIN erm_xref_hazard_cat
                           ON erm_cat.[catid] = erm_xref_hazard_cat.[catid])
               ON erm_hazard.[hazardid] = erm_xref_hazard_cat.[hazardid]
       INNER JOIN erm_hazardstatus
               ON erm_hazard.hazardstatusid = erm_hazardstatus.hazardstatusid
WHERE  (( ( erm_xref_hazard_cat.periodid ) = (SELECT Max(
                                             erm_xref_hazard_cat.periodid)
                                              FROM   erm_xref_hazard_cat) ))
ORDER  BY erm_xref_hazard_cat.periodid DESC,
          erm_hazard.hazardid,
          erm_cat.name  

Is this a Access SQL Size problem? Any ideas?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

MS Access is very particular with the parentheses in queries. When you have multi-table joins a pair of parentheses must be around each join:

SELECT ERM_HAZARD.HAZARDID AS PKTrans, 
    ERM_HAZARD.EXTREF AS No, 
    ERM_CAT.NAME AS CaseType, 
    ERM_HAZARD.CREATED as CaseDate, 
    ERM_HAZARD.NAME AS Title, 
    ERM_HAZARD.DESCRIPTION AS DescriptionTrans,  
    ERM_ORGANISATION.NAME AS ResponsibleUnit, 
    ERM_HAZARD.REVIEWDATE AS CaseDeadline, 
    ERM_HAZARDSTATUS.NAME as Status, 
    ERM_ORGANISATION.NAME AS UnitInCharge, 
    ERM_PERSONEL.NAME AS PersonInCharge 
FROM ((((ERM_PERSONEL 
INNER JOIN ERM_HAZARD 
    ON ERM_PERSONEL.[PERSONELID] = ERM_HAZARD.[PERSONELID])
INNER JOIN ERM_ORGANISATION 
    ON ERM_ORGANISATION.[ORGANISATIONID] = ERM_HAZARD.[ORGANISATIONID])
INNER JOIN ERM_XREF_HAZARD_CAT 
    ON ERM_HAZARD.[HAZARDID] = ERM_XREF_HAZARD_CAT.[HAZARDID])
INNER JOIN ERM_CAT 
    ON ERM_CAT.[CATID] = ERM_XREF_HAZARD_CAT.[CATID])
INNER JOIN ERM_HAZARDSTATUS 
    ON ERM_HAZARD.HAZARDSTATUSID = ERM_HAZARDSTATUS.HAZARDSTATUSID
WHERE ERM_XREF_HAZARD_CAT.PERIODID= (SELECT MAX(ERM_XREF_HAZARD_CAT.PERIODID) 
                                     FROM ERM_XREF_HAZARD_CAT) 
ORDER BY ERM_XREF_HAZARD_CAT.PERIODID DESC,  ERM_HAZARD.HAZARDID, ERM_CAT.NAME

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...