Re: Complex PLSQL-JSP problem .

jockey_sql_at_indya.com (jock) wrote in message news:<77a43c6a.0206290014.2543c8d0_at_posting.google.com>...
> Dear friends,
>
> I have a table with 3000 suppliers on it. Now,i want to rank these
> suppliers
> when the user clicks on "Submit" button on my JSP Form.
>
> The ranking function is not a simple "ORDER BY". I apply a function to
> each of the rows in the supplier table. The supplier who satisfies my
> conditions the best is ranked first.
>
> As an example, if my supplier has a lead time less than 20 days i give
> him 20 points. If he is in my state, i give him 20 points more. The
> supplier with the most points is my preferred supplier. I have half a
> dozen such criteria.
>
> What is the best method of doing this via JSP?
>
> **Create a temporary table with the ranked suppliers and then populate
> from this temporary table. The temporary table is created by calling a
> Stored procedure.
> Now, how do i generate the temporary table name and make sure its
> unique?
>
> **PLSQL Table-- How do i return a PLSQL Table to populate a JSP page?
>
> **Updating the base table is ruled out as there may be several users
> simaltaneously using the page.
>
> Some sample code or tips is deeply appreciated.
>
> Thank you very much.

i'm going to do the unthinkable and give a possible java solution in c.d.o.s ..

if you think the criteria you mention might be parameterizable and/or the front-end app might want to allow the user to re-sort based on different criteria, or different parameters, you could return all the supplier-related raw data from oracle, and just write a Comparator class (the comparator class can also take parameter inputs). it'd be pretty easy to code, and could be nice since re-sorting from the front-end wouldn't require another query to the db with a different "order by my_other_rank_func(supplierid)" or worse yet, a different "order by my_rank_func(supplierid, parameter1, parameter2, ..., parameterN)". also would get around any temp table or function based index issues.

of course, oracle is better suited to the numbers game, and memory might become an issue because this method requires potentially 3000 supplier objects (do you have any WHERE clauses to go with that ORDER BY?) at once, but it might help cut down on the number of roundtrips if you are going to do any re-sorting. Received on Mon Jul 01 2002 - 17:17:25 CDT