v.type = null is not a valid JPQL expression. You need to use IS NULL instead.
thanks,
-marina
James Sutherland 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
>>
>>
>
>