Ejb ql ORDER BY with null cmr

[Gilbert Debattista]

Hello

I have the following ejb ql statement :
SELECT OBJECT(x) FROM X x, IN(x.v) v WHERE v.id = ?1 ORDER BY x.country.name ASC

the problem is when the country in x is null. It seems that the statement fails when it encounters x.null.name since i'm getting all the results except for those rows where country is null. What am I doing wrong?
[ April 05, 2005: Message edited by: Gil Debattista ]

[Damanjit Kaur]

I have the following ejb ql statement :
SELECT OBJECT(c) FROM X x, IN(x.v) v WHERE v.id = ?1 ORDER BY x.country.name ASC

the problem is when the country in x is null. It seems that the statement fails when it encounters x.null.name since i'm getting all the results except for those rows where country is null. What am I doing wrong?

The query will return only those results where matching records for country exist in x. so if country is null its not able to match with any corresponding record in x hence no result for such row.

[Gilbert Debattista]

thanks Damanjit,
however i really would like to know if there is a workaround to it. I just want to get all records that match the query and, where x.country is not null, ordered by x.country.name.

[Damanjit Kaur]

I just want to get all records that match the query and, where x.country is not null, ordered by x.country.name.

I am sorry, if I haven't understood your point properly. As you said in your previous post that you are getting all the results except when country is null. In other sense,which means you are getting all results when country is not null.

Are you getting some error while running query that you mentioned in your first post?

[Gilbert Debattista]

Originally posted by Damanjit Kaur:


I am sorry, if I haven't understood your point properly. As you said in your previous post that you are getting all the results except when country is null.

That's right. I just want to sort the results according to country.name however I'm only recieving the rows in which country != null. I want all rows, including those in which country is null, sorted by country.name (obviously where country is not null). I don't know if this is possible however I'm finding the fact that excluding the rows where country == null is a bit strange.

Originally posted by Damanjit Kaur:

Are you getting some error while running query that you mentioned in your first post?

No, the only issue I'm having is that some rows are excluded, as I've explained before.

[Damanjit Kaur]

SELECT OBJECT(x) FROM X x, IN(x.v) v WHERE v.id = ?1 ORDER BY x.country.name ASC

When you specify x.country.name it traverses the path and hence matching will occur, however if you just specify country.name in ORDER BY clause then no path traversing from x and hence all country names including null should be there. But again problem is then you need to specify this in SELECT clause for country, which again means putting restrictions as to selection of records from x and country in SELECT clause.

That is how I read in EJB QL2.1 specifications.

[Gilbert Debattista]

Originally posted by Damanjit Kaur:


But again problem is then you need to specify this in SELECT clause for country, which again means putting restrictions as to selection of records from x and country in SELECT clause.

So I guess there is no apparent solution for this problem. A workaround might be to get the result set from the above ejbql statement and then append to it the result set from the following statement:
SELECT OBJECT(x) FROM X x, IN(x.v) v WHERE v.id = ?1 AND x.country IS NULL

[Damanjit Kaur]

Ya, but thats a very weird solution. Hope somebody else having solution, reads this thread and provides it.

[Gilbert Debattista]

Originally posted by Damanjit Kaur:
Ya, but thats a very weird solution. Hope somebody else having solution, reads this thread and provides it.

I agree 100% with you!

[Ken Loh]

Does JOIN work in EJB-QL ? You may want to UNION results with country <> null and those with results=NULL.

Originally posted by Gil Debattista:


So I guess there is no apparent solution for this problem. A workaround might be to get the result set from the above ejbql statement and then append to it the result set from the following statement:
SELECT OBJECT(x) FROM X x, IN(x.v) v WHERE v.id = ?1 AND x.country IS NULL

[Gilbert Debattista]

Originally posted by Ken Loh:
Does JOIN work in EJB-QL ? You may want to UNION results with country <> null and those with results=NULL.

No, EJB QL supports only traversing of cmp/cmr objects. However JBoss (which is the application server I am using) does support native SQL commands but that means that one would lose the abstraction of EJB-QL over the underlying database.