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]>:
> 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].
> 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.
