• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

EJB QL problem with SELECT DISTINCT COUNT

 
Gilbert Debattista
Greenhorn
Posts: 19
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 775
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Gilbert Debattista
Greenhorn
Posts: 19
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 775
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 457
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic