SQLException incorrect syntax near 'SELECT'

I can't figure this one out.

this query runs in sqlServer without error:

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)

Transformed it to @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;

changed the NOW() and added the link to the the pojo Id : tjmm.MainMission_SID = MainMission_SID

Anyone has idea where I have the fault in the formula?

Solution

Short story: wrap the content of @Formula with braces

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

Longer story: the @Formula mapping, represents "column like" expression. As documented here:

5.1.4.1.5. Formula

it can contain any statement resulting in select like this:

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

But this won't work

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

Because such statement must be wrapped in braces

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

And that is the issue with the @Formula above