Steve Muench wrote:
>
> | > if I have 10,000 sales people likely their leads and closed sales
> | > are likely not modeled as part of the Salesperson EB itself, but
> | > instead as a related Lead and Sale EB.
> |
> | You could also set up a two-way CMR relationship here for sales and the
> | person responsible. Thus, it doesn't really matter whether you ask the
> | sale for the salesman responsible, or the salesman for his sales. If the
> | salesman has access to his sales, then it can just as easily have a
> | convenience method on the interface to give a "total sales figure". That
> | takes care of the aggregation part.
>
> Right, this means that I can get an iterator over 10,000 Saleperson bean
> instances, and in Java, for each one, ask what their total sales figure is.
> That still doesn't help me find the top five of the whole company. I need
> to visit each one and ask for their total to be sure I've considered them all.
> For this you need to aggregate and order then filter...
>
EJB QL did not leave out ORDER BY because it was felt that it was not
needed. There are definitely a host of valid uses for it as well as many
cases where ordering in the app makes better sense.
EJB QL left it out because there were some technical issues that needed
to be resolved and there was not time to resolve them. Whether these
issues which centered around the choice of Java semantics or SQL
semantics could have been resolved or not for 2.0 is now a moot point.
ORDER BY is definitely at the top of the list of features to add to EJB
QL.
> | For the ordering part, I would take a different approach. If you DB has
> | to sort 10,000 objects and then just pick the top 5, that's a hugely
> | inefficient operation.
>
> This is precisely my point. The database *theoretically* has to aggregate
> and sort all 10,000, but a good database would never need to *actually*
> do that. Using indexes and perhaps materialized views, with a few
> disk I/O's the database could return the first five salespeople based
> on the ranked aggregates of their total sales. Twenty-five years of
> tuning have made relational DB's very good at this part.
>
> I know Oracle, so I know I can do a query like this:
>
> /* Return the top five salespeople based
> * on total sales for september 2001
> */
> select *
> from (
> select salesperson, sum(amount) as total
> from sales
> where sale_date between '01-SEP-2001' and '30-SEP-2001'
> group by salesperson
> order by total desc
> )
> where rownum <= 5 /* only the top 5 in aggregated, sorted order */
>
> | Instead, I would create some form of bar first -
> | pick all sales totals over some value (user defined with an input
> | parameter) and then sort on that.
>
> But, in general, the user (a top-level manager?) won't know what
> sales total value to pick here to guarantee the result she wants.
>
> | For example, have the finder locate the min and max sales totals
> | and calculate the top five percent of sales totals.
>
> To calculate the MIN and MAX you have to either use SQL with
> vendor-specific extensions, or iterate over all of your
> Salesperson EB's again, right? To the best of my knowledge,
> the BNF grammar for EJB QL does not include a MAX() or MIN()
> or SUM() aggregate function.
>
> The list of employees with sales totals greater than
> the top five percent of the MAX salesfigure may not return
> a list of the top five salespeople. It may only return 1.
> For example, if the very top salesperson was *way* ahead
> of the rest. For example (given just a list of 10 employees),
> if their sales totals were:
>
> 100000,80000,70000,60000,50000,40000,30000,20000,10000,9000
>
> Then the number of salespeople who sold more than 95% of the MAX
> of 100000 would be all those who sold more than 95000, which is
> just one person, not the top five like the manager wanted.
>
> | Use that as the return value of the EB finder and then sort the
> | bean references as per usual in a List. I'd come up with some useful
> | code, but my brain is too fried now (can't even get my CVS commit
> | messages right when I have them written down in front of me!). Will do
> | it in the morning.
>
> I'll be interested to learn some tricks from your example. Thanks!
>
> _____________________________________________________________________
> Steve Muench - Developer, Product Manager, XML Evangelist, Author
> "Building Oracle XML Applications" - www.oreilly.com/catalog/orxmlapp
>
> ===========================================================================
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
> of the message "signoff EJB-INTEREST". For general help, send email to
> [EMAIL PROTECTED] and include in the body of the message "help".
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
