OpenJPA
  1. OpenJPA
  2. OPENJPA-120

For Multi Table Entity subselect with field in second table gets DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: query
    • Labels:
      None
    • Environment:
      incubator 097

      Description

      For Multi Table Entity subselect with field in second table gets DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601

      the field salary is in table 2

      --select e from EmpBean e where (e.salary > (select sum(e.salary) from EmpBean e))
      --DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: );SUM(t3.salary) FROM;<table_expr>

      {prepstmnt 43647642 SELECT t0.empid, t0.bonus, t4.deptno, t4.budget, t5.empid, t5.bonus, t5.dept_deptno, t5.execLevel, t5.hireDate, t5.hireTime, t5.hireTimestamp, t5.home_street, t5.isManager, t5.name, t6.salary, t5.work_street, t4.name, t7.deptno, t7.budget, t7.mgr_empid, t7.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t8.street, t8.city, t8.state, t8.zip, t0.isManager, t0.name, t1.salary, t9.street, t9.city, t9.state, t9.zip FROM EmpBean t0 INNER JOIN empbean2 t1 ON t0.empid = t1.EmpBean_empid LEFT OUTER JOIN DeptBean t4 ON t0.dept_deptno = t4.deptno LEFT OUTER JOIN AddressBean t8 ON t0.home_street = t8.street LEFT OUTER JOIN AddressBean t9 ON t0.work_street = t9.street LEFT OUTER JOIN EmpBean t5 ON t4.mgr_empid = t5.empid LEFT OUTER JOIN DeptBean t7 ON t4.reportsTo_deptno = t7.deptno LEFT OUTER JOIN empbean2 t6 ON t5.empid = t6.EmpBean_empid INNER JOIN empbean2 t3 ON t2.empid = t3.EmpBean_empid WHERE (t1.salary > (SELECT SUM(t3.salary) FROM ))}

      [code=-104, state=42601]

      select x from EmpBean x where x.salary < (select max(y.salary) from EmpBean y where x.salary=y.salary)
      --DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;.salary) FROM WHERE;WHERE

      {prepstmnt 214568138 SELECT t0.empid, t0.bonus, t4.deptno, t4.budget, t5.empid, t5.bonus, t5.dept_deptno, t5.execLevel, t5.hireDate, t5.hireTime, t5.hireTimestamp, t5.home_street, t5.isManager, t5.name, t6.salary, t5.work_street, t4.name, t7.deptno, t7.budget, t7.mgr_empid, t7.name, t0.execLevel, t0.hireDate, t0.hireTime, t0.hireTimestamp, t8.street, t8.city, t8.state, t8.zip, t0.isManager, t0.name, t1.salary, t9.street, t9.city, t9.state, t9.zip FROM EmpBean t0 INNER JOIN empbean2 t1 ON t0.empid = t1.EmpBean_empid LEFT OUTER JOIN DeptBean t4 ON t0.dept_deptno = t4.deptno LEFT OUTER JOIN AddressBean t8 ON t0.home_street = t8.street LEFT OUTER JOIN AddressBean t9 ON t0.work_street = t9.street LEFT OUTER JOIN EmpBean t5 ON t4.mgr_empid = t5.empid LEFT OUTER JOIN DeptBean t7 ON t4.reportsTo_deptno = t7.deptno LEFT OUTER JOIN empbean2 t6 ON t5.empid = t6.EmpBean_empid INNER JOIN empbean2 t3 ON t2.empid = t3.EmpBean_empid WHERE (t1.salary < (SELECT MAX(t3.salary) FROM WHERE (t1.salary = t3.salary)))}

      [code=-104, state=42601]

        Activity

        Hide
        Ali Manji added a comment -

        Its might be that the errors experienced here occur under other scenarios as well.

        Am also getting this error, trying to persist an object that is essentially a composition of 2 other objects and a primary key.

        For instance AMember object is composed of an id field (generated value) of type long, an Address object, and a ContactInfo object.

        The first sql stmt (of serveral) generated that is executed is:

        INSERT INTO PrimaryMember () VALUES ()

        It is this above SQL stmt when executed in DB2 generates the error: SQLCODE: -104, SQLSTATE: 42601
        (which I confirmed by trying to execute directly against DB2).

        Show
        Ali Manji added a comment - Its might be that the errors experienced here occur under other scenarios as well. Am also getting this error, trying to persist an object that is essentially a composition of 2 other objects and a primary key. For instance AMember object is composed of an id field (generated value) of type long, an Address object, and a ContactInfo object. The first sql stmt (of serveral) generated that is executed is: INSERT INTO PrimaryMember () VALUES () It is this above SQL stmt when executed in DB2 generates the error: SQLCODE: -104, SQLSTATE: 42601 (which I confirmed by trying to execute directly against DB2).

          People

          • Assignee:
            Unassigned
            Reporter:
            George Hongell
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development