1 Reply Latest reply on Jun 22, 2004 4:13 AM by Alexey Loubyansky

    Problem with SQL92 generated finder

    chorizo Newbie

      The SQL92 EJB-QL compiler generated invalid SQL from a finder. The finder is:

      SELECT OBJECT(i) FROM Item i
      WHERE i.folder.project.path = ?1
      AND i.folder.path = ?2
      AND i.name = ?3
      AND i.deleted = FALSE

      The SQL generated is:
      SELECT t0_i.id, t0_i.name, t0_i.title, t0_i.type_id,
      t0_i.version, t0_i.date_created, t0_i.date_last_modified,
      t0_i.is_deleted, t0_i.last_modified_by_id, t0_i.created_by_id, t0_i.folder_id
      FROM item t0_i LEFT OUTER JOIN folder t2_i_folder ON t0_i.folder_id=t2_i_folder.id
      LEFT OUTER JOIN project t1_i_folder_project ON t0_i.project_id=t1_i_folder_project.id
      WHERE t1_i_folder_project.path = ? AND t2_i_folder.path = ? AND t0_i.name = ? AND t0_i.is_deleted = FALSE

      The error that follows is:
      java.sql.SQLException: ERROR: No such attribute t0_i.project_id

      The 3 tables involved are:
      item (id, name, title, ..., folder_id)
      folder (id, path, title, ..., parent_folder_id, project_id)
      project(id, path, title, ..., parent_project_id)

      The SQL should have t0_i.project_id replaced with t2_i_folder.project_id