| scalar subqueries in select supported? [message #933825] |
Fri, 05 October 2012 07:23  |
Eclipse User |
|
|
|
Hi all,
I have written a query using the Criteria API which does a multiselect()
and as part of that also includes some scalar subqueries and I've also
experimented with including exists() expressions referencing a subquery.
These queries don't cause any "parse time" exceptions but when these
queries are executed they don't always return the expected results.
I know scalar subqueries are not supported by JPQL in the select part,
but I was wondering if EclipseLink supports this at all using the
Criteria API or not.
Best regards,
--knut
|
|
|
|
| Re: scalar subqueries in select supported? [message #940146 is a reply to message #939176] |
Thu, 11 October 2012 06:57  |
| Eclipse User |
|
|
|
Hi James,
On 10/10/12 3:46 PM, James Sutherland wrote:> EclipseLink 2,4 does
support sub-queries in the SELECT clause in JPQL.
> It should work in criteria as well (ensure you are using 2.4).
>
I was testing with EclipseLink 2.3. This probably explains why I
sometimes got funny results.
I now see that EclipseLink 2.4 is documented to support scalar
sub-queries (aka "sub-selects") in the SELECT clause:
http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#SELECT_Clause.
I had missed that one. But that is very good news!
> Using EXISTS in the SELECT clause I don't think is allowed in the
> database, so this does not sound correct.
> What is your code, and what is the generated SQL?
>
Some databases which have BOOLEAN datatypes, like e.g. Derby, support
EXISTS in the SELECT clause. E.g.
select id, exists(select 1 from bar b where b.foo_id = f.id) has_bar
from foo f;
Of course that can also easily be expressed differently on other
databases, like Oracle, which don't support EXISTS expressions in the
SELECT clause.
Regards,
--knut
|
|
|
Powered by
FUDForum. Page generated in 0.04264 seconds
Home