Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » scalar subqueries in select supported?
scalar subqueries in select supported? [message #933825] Fri, 05 October 2012 11:23 Go to next message
Knut Wannheden is currently offline Knut WannhedenFriend
Messages: 296
Registered: July 2009
Senior Member
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 #939176 is a reply to message #933825] Wed, 10 October 2012 13:46 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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).

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?


James : Wiki : Book : Blog : Twitter
Re: scalar subqueries in select supported? [message #940146 is a reply to message #939176] Thu, 11 October 2012 10:57 Go to previous message
Knut Wannheden is currently offline Knut WannhedenFriend
Messages: 296
Registered: July 2009
Senior Member
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
Previous Topic:Unmarshalling through eclipselink moxy and jaxb
Next Topic:maven repo problem
Goto Forum:
  


Current Time: Sat Dec 20 02:21:10 GMT 2014

Powered by FUDForum. Page generated in 0.01598 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software