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?
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)
Joined: Apr 05, 2005
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
Joined: Jan 25, 2002
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.
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);
subject: EJB QL problem with SELECT DISTINCT COUNT