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.
