Hi Gary,
Ah, now I see what you are trying to do. You want the total number of new
cars per dealer, and in your SQL are relying on the fact that the SQL COUNT
function ignores null values. This does not really translate into JPQL or
an object query language, as it is very subtle SQL oriented. You may be
best off using a native SQL query for this, or executing two separate JPQL
queries, one for the count of dealers new cars, and one for the count of
dealer old cars and merging the two results in memory.
I still think there might be away to do this in JPQL, but cannot think of an
easy way. TopLink does support a CASE function which you could use in the
COUNT function to replace anything other than 'new' with null, and that
would work if JPQL supported the CASE function, but it does not, also some
databases do not support CASE either. If you database does support CASE,
then you can define the query using a TopLink ReportQuery instead of JPQL
and use the TopLink Expression caseStatement API.
Another workaround would be to define a OneToManyQueryKey in your TopLink
descriptor. Again this is quite advanced, but you could use a
DescriptorCustomizer in your persistence.xml to create a TopLink
OneToManyQueryKey "newVehicles" object that has a "selectionCriteria" that
includes the join and 'new' check and add this do your ClassDescriptor. You
should then be able to use this query-key in JPQL the same as you OneToMany
mapping.
Gary Jacobson wrote:
>
> Hi James
>
> You are correct that adding WHERE v.type IS NULL OR v.type = 'New' will
> bring back a list of dealers with either no vehicles or a new vehicle.
> However this has the effect of excluding dealers who only have old
> vehicles.
>
> I suspect this is a deficiency in the actual JPQL specification. No matter
> what combinations of IS NULL and IS EMPTY I use, it always generates a
> WHERE
> clause instead of an ON clause for the "v.type = 'New'" check, which
> immediately makes my SQL incorrect.
>
> Thanks
> Gary
>
> On 9/12/07, James Sutherland <jamesssss_at_yahoo.com> wrote:
>>
>>
>> What you are basically querying for is all Dealers who have either no
>> Vehicles or a "new" Vehicle.
>> You should be able to use an OR for this,
>> i.e.
>> WHERE (v.type = null) OR (v.type = 'New')
>>
>> But IS NULL and IS EMPTY should work as well, what was the JPQL you tried
>> and the SQL it produced?
>> Also what database are you using?
>>
>> ---
>> http://wiki.java.net/bin/view/People/JamesSutherland James Sutherland
>>
>>
>> Gary Jacobson wrote:
>> >
>> > Is there an equivalent to the SQL ON clause in JPQL?
>> >
>> > Let's say you want a list of car dealers and how many new vehicles
>> they're
>> > selling.
>> >
>> > In SQL you can say:
>> >
>> > SELECT d.name, count( v.id )
>> > FROM dealer d LEFT OUTER JOIN vehicle v
>> > ON v.dealer_id = d.dealer_id AND v.type = 'New'
>> > GROUP BY d.name
>> >
>> > In JPQL, you only have the WHERE clause which limits your results,
>> making
>> > the outer join useless:
>> >
>> > SELECT d.name, count( v.id )
>> > FROM dealer d LEFT OUTER JOIN d.vehicleList v
>> > WHERE v.type = 'New'
>> > GROUP BY d.name
>> >
>> > (My actual query is a fair amount more complicated, and I've tried
>> messing
>> > around with IS NULL and IS EMPTY to no avail)
>> >
>> > Is there any way to do this?
>> >
>> > Thanks
>> > Gary
>> >
>> >
>>
>> --
>> View this message in context:
>> http://www.nabble.com/LEFT-OUTER-JOIN---ON-clause-equivalent--tf4430420.html#a12641034
>> Sent from the java.net - glassfish persistence mailing list archive at
>> Nabble.com.
>>
>
>
--
View this message in context: http://www.nabble.com/LEFT-OUTER-JOIN---ON-clause-equivalent--tf4430420.html#a12654898
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.