This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes EJB and other Java EE Technologies and the fly likes Ejb ql ORDER BY with null cmr Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Ejb ql ORDER BY with null cmr" Watch "Ejb ql ORDER BY with null cmr" New topic
Author

Ejb ql ORDER BY with null cmr

Gilbert Debattista
Greenhorn

Joined: Apr 05, 2005
Posts: 19
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
Ranch Hand

Joined: Oct 18, 2004
Posts: 346
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
Greenhorn

Joined: Apr 05, 2005
Posts: 19
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
Ranch Hand

Joined: Oct 18, 2004
Posts: 346
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
Greenhorn

Joined: Apr 05, 2005
Posts: 19
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
Ranch Hand

Joined: Oct 18, 2004
Posts: 346
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
Greenhorn

Joined: Apr 05, 2005
Posts: 19
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
Ranch Hand

Joined: Oct 18, 2004
Posts: 346
Ya, but thats a very weird solution. Hope somebody else having solution, reads this thread and provides it.
Gilbert Debattista
Greenhorn

Joined: Apr 05, 2005
Posts: 19
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
Ranch Hand

Joined: Feb 16, 2005
Posts: 190
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
Greenhorn

Joined: Apr 05, 2005
Posts: 19
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Ejb ql ORDER BY with null cmr
 
Similar Threads
IS NULL selection to CMP Entity EJB from DB
Error compiling EJB-QL statement ''; FROM not found
EJB QL Problem in both EJB2.0 & EJB 2.1...
ORDER BY clause in EJB QL valid for EJB2.0?
CDATA in EJB-ql