openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Hahn, Markus" <Markus.H...@ottogroup.com>
Subject JPQL IN-clause generates "=" in SQL
Date Tue, 08 Jan 2008 08:30:56 GMT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>JPQL IN-clause generates &quot;=&quot; in SQL</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->

<P><FONT SIZE=2 FACE="Arial">Hi folks,</FONT>

<BR><FONT SIZE=2 FACE="Arial">the following JPQL leads into problems:</FONT>

<BR><FONT COLOR="#2A00FF" SIZE=2 FACE="Courier New">SELECT this FROM com.ottogroup.buying.process.dispoquota.MerchandisePlanKeyData
this WHERE this.planningPeriod.id = :ppid AND this.buyingGroup.id = :bgid AND this.merchandiseGroup
IS NULL AND this.productGroup IN (SELECT i.productGroup FROM Item i JOIN i.itemPlannings ip
JOIN ip.stockProductLine spl JOIN spl.merchandiseGroup mg JOIN mg.buyingGroup bg WHERE bg.id
= :bgid)</FONT></P>

<P><FONT SIZE=2 FACE="Arial">SQL generation transforms the IN to a &quot;=&quot;
which leads to the error message: &lt;2|false|0.9.5-incubating-CR304301v2&gt; org.apache.openjpa.util.StoreException:
ORA-01427: single-row subquery returns more than one row</FONT></P>

<P><FONT SIZE=2 FACE="Arial">&nbsp;{prepstmnt 17320121 SELECT t0.ID, t0.TSUPDATE,
t0.ID_BUYINGGROUP, t0.ID_DISPOSAL, t0.FLOORSERVICELEVELPRC, t0.LIMITRETURNRATEPRC, t0.LIMITSENTPRC,
t0.LIMITSURPLUSPRS, t0.ID_MERCHANDISEGROUP, t0.ID_PLANNINGPERIOD, t0.ID_PRODUCTGROUP FROM
BUYING.MERCHANDISEPLANKEYDATA t0 WHERE (t0.ID_PLANNINGPERIOD = ? AND t0.ID_BUYINGGROUP = ?
AND t0.ID_MERCHANDISEGROUP IS NULL AND t0.ID_PRODUCTGROUP = (SELECT t6.ID FROM BUYING.ITEM
t1, BUYING.ITEMPLANNING t2, BUYING.STOCKPRODUCTLINE t3, BUYING.MERCHANDISEGROUP t4, BUYING.BUYINGGROUP
t5, SYSP.PRODUCTGROUP t6 WHERE (t4.ID_BUYINGGROUP = ? AND 1 = 1) AND t1.ID = t2.ID_ITEM AND
t1.ID_PRODUCTGROUP = t6.ID AND t2.ID_STOCKPRODUCTLINE = t3.ID AND t3.ID_MERCHANDISEGROUP =
t4.ID AND t4.ID_BUYINGGROUP = t5.ID) AND t0.ID_PRODUCTGROUP IS NOT NULL) [params=(long) 82,
(long) 48, (long) 48] [reused=0]} [code=1427, state=21000]</FONT></P>

<P><FONT SIZE=2 FACE="Arial">is it a known bug? something wrong with my JPQL?
Any hints or workarounds?</FONT>

<BR><FONT SIZE=2 FACE="Arial">Thanks in advance, Markus Hahn, Otto, Germany</FONT>
</P>

</BODY>
</HTML>

Mime
View raw message