Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Message-ID: <16906015.1198468003061.JavaMail.jira@brutus>
Date: Sun, 23 Dec 2007 19:46:43 -0800 (PST)
From: "Ali Manji (JIRA)" <jira@apache.org>
To: dev@openjpa.apache.org
Subject: [jira] Commented: (OPENJPA-120) For Multi Table Entity subselect
 with field in second table gets DB2 SQL error: SQLCODE: -104, SQLSTATE:
 42601
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/OPENJPA-120?page=3Dcom.atlassia=
n.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12554213 ]=20

Ali Manji commented on OPENJPA-120:
-----------------------------------

Its might be that the errors experienced here occur under other scenarios a=
s 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:=20

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).


> For Multi Table Entity subselect with field in second table gets DB2 SQL =
error: SQLCODE: -104, SQLSTATE: 42601
> -------------------------------------------------------------------------=
-------------------------------------
>
>                 Key: OPENJPA-120
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-120
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: query
>         Environment: incubator 097
>            Reporter: George Hongell
>
> 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 Em=
pBean e))=20
> --DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: );SUM(t3.salar=
y) FROM;<table_expr> {prepstmnt 43647642 SELECT t0.empid, t0.bonus, t4.dept=
no, t4.budget, t5.empid, t5.bonus, t5.dept_deptno, t5.execLevel, t5.hireDat=
e, 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.na=
me, 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.ci=
ty, t9.state, t9.zip FROM EmpBean t0 INNER JOIN empbean2 t1 ON t0.empid =3D=
 t1.EmpBean_empid LEFT OUTER JOIN DeptBean t4 ON t0.dept_deptno =3D t4.dept=
no LEFT OUTER JOIN AddressBean t8 ON t0.home_street =3D t8.street LEFT OUTE=
R JOIN AddressBean t9 ON t0.work_street =3D t9.street LEFT OUTER JOIN EmpBe=
an t5 ON t4.mgr_empid =3D t5.empid LEFT OUTER JOIN DeptBean t7 ON t4.report=
sTo_deptno =3D t7.deptno LEFT OUTER JOIN empbean2 t6 ON t5.empid =3D t6.Emp=
Bean_empid INNER JOIN empbean2 t3 ON t2.empid =3D t3.EmpBean_empid WHERE (t=
1.salary > (SELECT SUM(t3.salary) FROM ))} [code=3D-104, state=3D42601]=20
> select x from EmpBean x where x.salary < (select max(y.salary) from EmpBe=
an y where x.salary=3Dy.salary)=20
> --DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;.salary) FRO=
M  WHERE;WHERE {prepstmnt 214568138 SELECT t0.empid, t0.bonus, t4.deptno, t=
4.budget, t5.empid, t5.bonus, t5.dept_deptno, t5.execLevel, t5.hireDate, t5=
.hireTime, t5.hireTimestamp, t5.home_street, t5.isManager, t5.name, t6.sala=
ry, t5.work_street, t4.name, t7.deptno, t7.budget, t7.mgr_empid, t7.name, t=
0.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, t=
9.state, t9.zip FROM EmpBean t0 INNER JOIN empbean2 t1 ON t0.empid =3D t1.E=
mpBean_empid LEFT OUTER JOIN DeptBean t4 ON t0.dept_deptno =3D t4.deptno LE=
FT OUTER JOIN AddressBean t8 ON t0.home_street =3D t8.street LEFT OUTER JOI=
N AddressBean t9 ON t0.work_street =3D t9.street LEFT OUTER JOIN EmpBean t5=
 ON t4.mgr_empid =3D t5.empid LEFT OUTER JOIN DeptBean t7 ON t4.reportsTo_d=
eptno =3D t7.deptno LEFT OUTER JOIN empbean2 t6 ON t5.empid =3D t6.EmpBean_=
empid INNER JOIN empbean2 t3 ON t2.empid =3D t3.EmpBean_empid WHERE (t1.sal=
ary < (SELECT MAX(t3.salary) FROM  WHERE (t1.salary =3D t3.salary)))} [code=
=3D-104, state=3D42601]=20

--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.