what is the difference in the following EJBQL queries?

[Bob Walker Jr]

Could anybody please tell me the difference in the following queries?
1. SELECT object(l) from Order o, IN(o.lineItems) l
2. SELECT o.lineItems from Order o
3. SELECT object(l) from LineItem l
Is the first one a valid query? Are the queries same?

[Karthik Guru]

Originally posted by Bob Walker Jr:
Could anybody please tell me the difference in the following queries?
1. SELECT object(l) from Order o, IN(o.lineItems) l
2. SELECT o.lineItems from Order o
3. SELECT object(l) from LineItem l
Is the first one a valid query? Are the queries same?

Under the assumption that lineItems is a CMR field on order,
order (1) --> (Many) LineItems
1 --> valid (will return all LineItems for all orders)
2 --> Invalid (a collection based CMR field can NEVER be "select" ed)
3 --> valid (will return all LineItems).
1) and 3) are essentially the same.

[Bob Walker Jr]

I thought that 1 and 2 are essentially same. They should both return only those lineitems that are being references by orders. While 3 will return all lineitems existing in the database.
Why is 2 invalid? Could you please explain a little bit more about it?

[Karthik Guru]

Why is 2 invalid? Could you please explain a little bit more about it?

SELECT clause can select only single-valued expression.
Since lineItem*s* is a Collection and hence multi valued, it cannot be selected.
You have to use the IN operator to first select each of the collection constituents into a different variable (l) which can be selected since it represents a single lineItem in the lineItems collection. So 1) is correct and 2) is not. Does that help?

While 3 will return all lineitems existing in the database.

Oh Ok. I assumed that Line items can exist only for an Order.
If that is not the case, then 1 & 3 are not the same as you have correctly pointed out.