Re: Complex PLSQL-JSP problem .

Create a pl/sql function and use it as one ofthe columns. For example, let's say that the function is called rankSupplier and take supplierid as an argument and it returns a number higher is better then something like this will work

select ... from mytable where ... order by ranksupplier(supplierid) desc or
select ranksupplier(supplierid),... from mytable where ... order by 1 desc; 1 is the number of the column the function is in

Jim

"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 Sat Jun 29 2002 - 08:20:31 CDT