3 Replies Latest reply on Nov 18, 2005 8:39 AM by Mario Burdman

    Syntax Error on Generated SQL

    michael lee Newbie

      Hi,

      The sql generated from my ejbSelect method is as follows:

      SELECT t0_r.name
      FROM operator t1_o, roleTable t0_r
      WHERE t1_o.name = ?
      AND
      (EXISTS
      (SELECT t3_o_roles_RELATION_TABLE.projectID,
      t3_o_roles_RELATION_TABLE.roleName
      FROM operatorRoles t3_o_roles_RELATION_TABLE
      WHERE t1_o.name=t3_o_roles_RELATION_TABLE.operatorName
      AND t0_r.projectID=t3_o_roles_RELATION_TABLE.projectID AND
      t0_r.name=t3_o_roles_RELATION_TABLE.roleName)
      )
      AND t0_r.name = ?
      AND t0_r.projectID = ?

      The backend database (Sybase ASE 12.5) complain about the ',' in Select clause WITHIN Exists. If only one column is specified or use '*', the statement is okay. (primary key for operatorRoles contain 2 columns and this may be the reason why Jboss generated 2 column in select).

      I am not sure this is the problem on Jboss or Sybase, but is there any mean i can control the SQL generated?

      TIA
      Michael