Re: Complex PLSQL-JSP problem .

My ranking function is dependent on the user input. The user determines on a scale how important each parameter is. For example for lead time, i may have a score of 100. 100 is user inputted. All supplier who have lead times within the specified lead time(lead time inputted by user again)will have 100 added to their overall score. Like lead time , i have several such criterion. The supplier with the largest score is ranked first.

I use Oracle 8i.

regards

Right now, i have decided on a PLSQL function which ranks them and puts them into a temporary table
Then i read from the temporary table.

thanks.

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:<afq1vb$5hh$1_at_babylon.agtel.net>...
> If the ranking function does not depend on user input and other rows
> in the table, it sounds like a good use of a functional index. You create
> a deterministic ranking function in PL/SQL which returns some ranking
> value for each row in a table, and then you create an index on this
> function. Selecting my_rank(id) from my_table order by my_rank(id)
> will return results very fast in this case (you will be penalized with delays
> on index creation and inserts/updates as Oracle will have to call your
> function for new/updated rows). If your function depends on other rows,
> this method probably does not apply - function will not be deterministic in
> this case (it will not always return the same result given the same input).
>
> --
> Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.
>
>
> "jock" <jockey_sql_at_indya.com> 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.
Received on Thu Jul 04 2002 - 15:54:20 CDT