Hi,
> v.type = null is not a valid JPQL expression. You need to use IS NULL
> instead.
Yes, you need to use IS NULL instead or comparing with NULL using =.
I propose to check the identification variable v instead of checking the
field 'type':
... WHERE (v IS NULL) OR (v.type = 'New')
Regards Michael
>
> 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
>>>
>>>
>>
>>
--
Tech_at_Spree Engineering GmbH Tel.: +49/(0)30/235 520-33
Buelowstr. 66 Fax.: +49/(0)30/217 520-12
10783 Berlin mailto:mbo.tech_at_spree.de
Geschaeftsfuehrung: Anna-Kristin Proefrock
Sitz Berlin, Amtsgericht Charlottenburg, HRB 564 52