Hello Lukas,
Thanks for your answer. It seems that I oversimplified my query to present reproduction steps. I need to have the first activity for each instance that fulfills some criteria. I did work around the bug using a derived table like you suggested, but inside the derived table I GROUP BY ACTIVITY.INSTANCE_DBID_ and try to select a unique row for each group (using a pretty bad hack that I am not happy with). François PS : Just out of curiosity (this might not solve this particular problem), but why don’t you emulate limit offset in Oracle with a window function like you do in DB2 and SQL SERVER 2008 ? On Wednesday, July 15, 2015 at 12:31:57 PM UTC+2, Lukas Eder wrote: > > Hello, > > I'm sorry for the delay in this matter. > > It's true that the emulation of the LIMIT clause introduces its own set of > caveats in Oracle, prior to 12c when OFFSET .. FETCH is finally supported. > > In your case, the issue is easily resolved by avoiding the unnecessary > correlated subquery and moving the "TOP" clause into a derived table, > instead. So, rather than this: > > select count(*) > from NOVA_PROCESS_INST INST > join NOVA_ACTIVITY_INST ACTIVITY > on INST.DBID_ = ACTIVITY.INSTANCE_DBID_ > where ( > ACTIVITY.DBID_ = ( > select V0 DBID_ > from ( > select > X.V0, > rownum RN > from ( > select ACTIVITY_1.DBID_ V0 > from NOVA_ACTIVITY_INST ACTIVITY_1 > where ( > INST.DBID_ = ACTIVITY_1.INSTANCE_DBID_ > ) > order by ACTIVITY_1.ACTIVITY_ID_ desc > > ) X > where rownum <= (0 + 1) > ) > where RN > 0 > ) > ); > > You could write this: > > select count(*) > from NOVA_PROCESS_INST INST > join ( > select V0 DBID_ > from ( > select X.V0, > rownum RN > from ( > select ACTIVITY_1.DBID_ V0 > from NOVA_ACTIVITY_INST ACTIVITY_1 order by > ACTIVITY_1.ACTIVITY_ID_ *desc > * ) X where rownum <= (0 + 1) ) where RN > 0 > ) ACTIVITY > on INST.DBID_ = ACTIVITY.INSTANCE_DBID_ > > Or simpler, without the Oracle-specific emulated SQL > > select count(*) > from NOVA_PROCESS_INST INST > join ( > select ACTIVITY_1.DBID_ V0 > from NOVA_ACTIVITY_INST ACTIVITY_1 order by ACTIVITY_1.ACTIVITY_ID_ > *desc > * limit 1 ) ACTIVITY > on INST.DBID_ = ACTIVITY.INSTANCE_DBID_ > > I hope this helps,Lukas > > 2015-07-02 16:45 GMT+02:00 François Devémy <[email protected] > <javascript:>>: > >> Hello everyone, >> >> >> I have a problem in Oracle: >> I am using a correlated subquery with a limit clause. >> But two things collade to create a bug: >> 1) limit clause is not supported in Oracle 11, so the limit is rendered >> (currently) as a nested select >> 2) there is a limitation to the depth of the correlation: according to >> the aofficial doc >> (at >> http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357 >> ) >> "Oracle performs a correlated subquery when a nested subquery references >> a column from a table referred to a parent statement one level above the >> subquery" >> So you can reference only one level deep. (See also >> http://stackoverflow.com/questions/1233910/is-there-a-nesting-limit-for-correlated-subqueries-in-oracle >> ) >> >> So the limit clause adds one level of depth to the subquery, and now >> Oracle cannot recognize the correlation. >> >> For an idea, this is the query I use: >> >> final SelectQuery<Record1<Integer>> instanceQuery = >> using.selectCount().getQuery(); >> instanceQuery.addFrom(INST); >> instanceQuery.addJoin(ACTIVITY, INST.DBID_.equal(ACTIVITY.INSTANCE_DBID_)); >> instanceQuery.addConditions(ACTIVITY.DBID_.equal( >> select(ACTIVITY_1.DBID_) >> .from(ACTIVITY_1) >> .where(INST.DBID_.equal(ACTIVITY_1.INSTANCE_DBID_)) >> >> .orderBy(ACTIVITY_1.ACTIVITY_ID_.desc()) >> .limit(1))); >> >> >> This is what is generated: >> >> select count(*) >> from NOVA_PROCESS_INST INST >> join NOVA_ACTIVITY_INST ACTIVITY >> on INST.DBID_ = ACTIVITY.INSTANCE_DBID_ >> where ( >> ACTIVITY.DBID_ = ( >> select V0 DBID_ >> from ( >> select >> X.V0, >> rownum RN >> from ( >> select ACTIVITY_1.DBID_ V0 >> from NOVA_ACTIVITY_INST ACTIVITY_1 >> where ( >> INST.DBID_ = ACTIVITY_1.INSTANCE_DBID_ >> ) >> order by ACTIVITY_1.ACTIVITY_ID_ desc >> >> ) X >> where rownum <= (0 + 1) >> ) >> where RN > 0 >> ) >> ); >> >> >> INST.DBID_ = ACTIVITY_1.INSTANCE_DBID_ >> * >> ERROR at line 16: >> ORA-00904: "INST"."DBID_": invalid identifier >> >> >> And this query works fine in MYSQL and PostgreSQL. >> What can I do? >> >> François >> >> -- >> You received this message because you are subscribed to the Google Groups >> "jOOQ User Group" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
