Hi! Sorry for not replying until now, have been on 4 weeks of vacation.
I'm glad to see that it was actually a bug because I was starting to think that I was doing something terribly wrong :) If i remember everything correct I also tried the latest version back then (1.2.1) and the outcome was the same but since im not 100% sure I will try it again asap and report back. Thanks for helping! / Ulf Michael Dick wrote: > > 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. >> > > -- View this message in context: http://n2.nabble.com/Problems-using-select-in-select-to-exclude-results-tp3270337p3436305.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
