Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes EJB and other Java EE Technologies and the fly likes EJB QL problem with SELECT DISTINCT COUNT Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "EJB QL problem with SELECT DISTINCT COUNT" Watch "EJB QL problem with SELECT DISTINCT COUNT" New topic
Author

EJB QL problem with SELECT DISTINCT COUNT

Gilbert Debattista
Greenhorn

Joined: Apr 05, 2005
Posts: 19
Hello guys & gals,
this is a very strange problem that I've encountered. I need to execute some dynamic ejb ql, which I had successfully done before. However this time I need to return a count of all distinct records that match a certain criteria. The following is an example of the dynamic ejb ql that is being generated:

Though the code looks good (at least I cannot find any possible pitfalls) I am not getting a count of all distinct records. Instead I am getting a count of all the records that match the search criteria... which may be including duplicate records! As far as I know the SELECT DISTINCT COUNT works well JBoss 4.0.1, which is the EJB container I'm using right now.
Pleeeaase, can anyone point me in the right direction?
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Inigo Montoya: You keep using that word. I do not think it means what you think it means.


Sorry, just couldn't resist the Princess Bride reference.

I think you are asking for a distinct count. In other words, you are asking for counts, but you don't want any duplicate counts. Not the same thing as wanting to count records, but not duplicate records. Suspect you have entered the nested subquery zone.


Reid - SCJP2 (April 2002)
Gilbert Debattista
Greenhorn

Joined: Apr 05, 2005
Posts: 19
Well, I thought I were doing that mistake until I fould out this example : EJB 2.1 Adds New Funtionality to EJB QL (look under the DISTINCT, nulls, and empty arguments section). To summerize that example here, the ejb ql
returns a count of unique zip addresses.
Regarding my code, I am doing the same thing. The strangest thing is that if I replace COUNT from the statement with OBJECT, only the correct number of unique records are returned but the COUNT function does not seem to be counting the unique records.
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Makes sense re: what you expect, not like there is some kind of 'group by' clause to imply you should expect multiple counts, but it sounds like that is the behaviour you are getting.
Bill Shirley
Ranch Hand

Joined: Nov 08, 2007
Posts: 457
Yes, I just ran across the same problem.

p195, 5th Edition, Enterprise JavaBeans 3.0.
Chapter 9: Queries and EJB QL



should be



which makes sense,
but I also know SQL just well enough to know that its syntax doesn't make sense, so I was trusting the book,

It is on the list of unconfirmed errata. It's been my experience that 95% of the unconfirmed errata on O'reilly books are usually actual errata - i guess they never bother to verify unless the book is being reprinted.
[ May 29, 2008: Message edited by: Bill Shirley ]

Bill Shirley - bshirley - frazerbilt.com
if (Posts < 30) you.read( JavaRanchFAQ);
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: EJB QL problem with SELECT DISTINCT COUNT
 
Similar Threads
EJB QL query from a mock exam
2 questions about EJB-QL
Data Access Object Design Questions
Search Appllication
Ejb ql ORDER BY with null cmr