Hi Ulf,
This is a bug. Some of these bugs have been addressed in
OPENJPA-681<http://issues.apache.org/jira/browse/OPENJPA-681>(fixed in
1.2.1) but there are some that still exist.
Catalina & Fay (on the dev team) have done a lot of work to improve how
OpenJPA handles subqueries. I'm taking a look at their approach now and
seeing whether it can be merged into earlier releases (like 1.2.2).
Could you try running with 1.2.1 and see that helps? If you're hitting a
scenario that isn't covered by OPENJPA-681 that would be a good data point
for determining whether we need to merge Catalina & Fay's changes.
Hope this helps,
-mike
On Fri, Jul 17, 2009 at 1:08 AM, ulfherge <ulf.he...@capgemini.com> wrote:
>
> Hi everyone I have this quite simple thing that I want to do but it doesnt
> seem to work the way I want, actually it looks like a bug to me.
>
> Here is my original query, allt it does is simply to select all ArendeEO
> and
> exlude some of them which does not satisfy the inner select clause
>
> Query q = em.createQuery("SELECT arende from ArendeEO arende JOIN
> arende.diarieNr diarieNr WHERE arende.arID NOT IN(SELECT arende.arID from
> ArendeEO arende WHERE arende.status.id = 3 AND arende.arendeTyp.id = 10)
> AND
> diarieNr.diarieNr LIKE '%09' ");
>
> This does NOT work, it just throws me an exception like this (shortened
> down
> a bit but the essentials are there)
>
> openjpa-1.2.0-r422266:683325 nonfatal general error>
> org.apache.openjpa.persistence.PersistenceException: ORA-00904: "T1"."ID":
> invalid identifier
> {prepstmnt 32778033 SELECT t0.arID, t0.arbetsstegDatum, t4.id,
> t4.arbetsuppgift, t4.namn, t4.procStodVSys FROM T_SU_ARENDE t0,
> T_SU_ARENDE_DIARIENR t3, T_SU_ARENDE_ARBETSSTEG t4, T_SU_ARENDETYP t5,
> T_SU_ARENDE_DIARIENR t6, T_SU_GALDENAR t7, T_SU_GALDENAR_ANSTFORH t8,
> T_SU_GALDENAR_BOSTADSFORH t9, T_SU_BUDGSKULDRADG t10, T_SU_OMBUD t11,
> T_SU_GALDENAR_SKALIGHET t12, T_SU_GALDENAR_UTBILDNING t13,
> T_SU_HANDLINGSDATA t14, T_SU_OMPROV_BORGENARDATA t15, T_SU_ARENDESTATISTIK
> t16, T_SU_ARENDE_STATUS t17 WHERE (NOT (t0.arID IN (SELECT t2.arID FROM
> T_SU_ARENDE_DIARIENR t1, T_SU_ARENDE t2 WHERE (t2.statusId = ? AND
> t2.arendeTypId = ?))) AND t3.diarieNr LIKE ? ESCAPE '\') AND t0.DIARIEID =
> t1.id AND t0.arbetsstegId = t4.id(+) AND t0.arendeTypId = t5.id(+) AND
> t0.DIARIEID = t6.id(+) AND t0.gldID = t7.gldID(+) AND t0.handlingsDataId =
> t14.id(+) AND t0.arID = t15.ARID(+) AND t0.arID = t16.ARID(+) AND
> t0.statusId = t17.id(+) AND t7.gldAnfoID = t8.gldAnfoID(+) AND
> t7.gldBofoID
> = t9.gldBofoID(+) AND t7.buskid = t10.buskID(+) AND t7.ombid = t11.ombID(+)
> AND t7.gldID = t12.GLDID(+) AND t7.gldutbldID = t13.gldUtbildningID(+)
> [params=(long) 3, (long) 10, (String) %09]} [code=904, state=42000]
> at
> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4238)
> at
>
> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4203)
> at
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
> ....
>
> When looking at the code I understand it doesnt work properly because
> becase
> T1 is never declared so how can it be used? A little odd and it looks like
> openJPA builds a non functional SELECT clause.
>
>
> When trying with a native query like this it works just fine:
>
> Query q2 = em.createNativeQuery("SELECT * FROM T_SU_ARENDE where arID NOT
> IN(SELECT arID FROM T_SU_ARENDE WHERE arendetypid = 10 AND statusid = 3)");
>
>
> This is fine and i could use the native query solution but the problem here
> is that we are using a class to dynamically build our search queries and
> the
> native queries would require us to rewrite a lot of code and it would be
> ALOT more complex.
>
> Is it a bug or am I just doing it the wrong way? I tried a couple of
> different ways but they all end up with the same exception.
>
> / Ulf
> --
> View this message in context:
> http://n2.nabble.com/Problems-using-select-in-select-to-exclude-results-tp3270337p3270337.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
