“SELECT”附近的SQLException语法不正确

问题描述：

这个查询运行SQLSERVER中没有错误：

SELECT  COUNT(tjmm.MAINMISSION_PARTICIPANT_SID) 
FROM   T_MOVEMENT_LEG ml INNER JOIN 
       TJ_MVLG_PARTMOV tjmp ON ml.MOVEMENTLEG_SID = tjmp.MOVEMENTLEG_SID INNER JOIN 
       T_MAIN_MISSION mm INNER JOIN 
       T_MISSION_PARTICIPANTS mp ON mm.DETCO_MISSIONPARTICIPANT_SID = mp.MISSION_PARTICIPANT_SID INNER JOIN 
       T_PARTICIPANT_MOVEMENT pm ON mp.MISSION_PARTICIPANT_SID = pm.MISSION_PARTICIPANT_SID INNER JOIN 
       TJ_MMIS_MISPART tjmm ON mm.MAINMISSION_SID = tjmm.MAINMISSION_SID AND 
       mp.MISSION_PARTICIPANT_SID = tjmm.MISSION_PARTICIPANT_SID ON 
       tjmp.PARTICIPANTMOVEMENT_SID = pm.PARTICIPANTMOVEMENT_SID 
WHERE   (mm.IN_USE = 1) AND (mp.IS_OPS = 1) AND (pm.IS_OPS = 1) AND 
       (ml.LEGEFFECTIVE_STARTDATE < { fn NOW() }) AND (ml.LEGEFFECTIVE_ENDDATE > { fn NOW() }) AND 
       (ml.IS_CANCELED = 0)

它转化到@Formula：

@Formula("select count(tjmm.MAINMISSION_PARTICIPANT_SID) " 
     + "FROM T_MOVEMENT_LEG ml INNER JOIN " 
     + "TJ_MVLG_PARTMOV tjmp ON ml.MOVEMENTLEG_SID = tjmp.MOVEMENTLEG_SID INNER JOIN " 
     + "T_MAIN_MISSION mm INNER JOIN " 
     + "T_MISSION_PARTICIPANTS mp ON mm.DETCO_MISSIONPARTICIPANT_SID = mp.MISSION_PARTICIPANT_SID INNER JOIN " 
     + "T_PARTICIPANT_MOVEMENT pm ON mp.MISSION_PARTICIPANT_SID = pm.MISSION_PARTICIPANT_SID INNER JOIN " 
     + "TJ_MMIS_MISPART tjmm ON mm.MAINMISSION_SID = tjmm.MAINMISSION_SID AND " 
     + "mp.MISSION_PARTICIPANT_SID = tjmm.MISSION_PARTICIPANT_SID ON " 
     + "tjmp.PARTICIPANTMOVEMENT_SID = pm.PARTICIPANTMOVEMENT_SID " 
     + "WHERE (mm.IN_USE = 1) AND (mp.IS_OPS = 1) AND (pm.IS_OPS = 1) AND " 
     + "tjmm.MainMission_SID = MainMission_SID AND" 
     + "(ml.LEGEFFECTIVE_STARTDATE < NOW()) AND (ml.LEGEFFECTIVE_ENDDATE > NOW()) AND " 
     + "(ml.IS_CANCELED = 0)") 
private Integer opsCount;

改变了NOW()并添加链接到该POJO编号：tjmm.MainMission_SID = MainMission_SID

任何人都知道我在公式中有什么缺点？

我看到你在年底错过了空间问题：+“tjmm.MainMission_SID = MainMission_SID AND” – Pienterekaak 2014-10-06 15:31:10

@Pienterekaak aargg，无法测试到明天但这确实是一个很好的候选人，谢谢 – chillworld 2014-10-06 15:32:38

答

简单地说：包裹@Formula的用大括号

// instead of this 
// @Formula("select count(tjmm.MAINMISSION_PARTICIPANT_SID) " 
// use this 
@Formula("(select count(tjmm.MAINMISSION_PARTICIPANT_SID) " 
      ... 
     ")")

更长故事内容：所述@Formula映射，表示表述 “柱状”。这里记载：

5.1.4.1.5. Formula

它可以包含导致选择这样的陈述：

SELECT 
    column1,   
    column1 + column2, // expecting columns contain number 
    column1 * column2, 
    ...

但是，这是行不通的

SELECT 
    column1,   
    ... 
    SELECT count(*) FROM OtherTable, // this won't work

因为这样声明必须用大括号

包装

SELECT 
    column1,   
    ... 
    (SELECT count(*) FROM OtherTable), // working while wrapped with()

这与@Formula上述

“SELECT”附近的SQLException语法不正确

相关推荐