Sorting query by a computed key.

I'd like to sort a result set based on a single-valued computation derived from the data in each row.

In other words, if I have a function COMPUTE_KEY(RowRecord r) which returns a number derived from the data from one row passed into it, then my select might look something like this:

select *
  from employees
  where salary > 50000
  order by column1, column2, COMPUTE_KEY(), column3

I'm working with Java/JDBC on Oracle 7.3.4, soon to be Oracle 8.1.5 (8i). The client is implemented in Java 1.2.

I imagine I could do what I want with PL/SQL (which I don't know, yet) and I am not averse to using JDBC to dynamically compose a function which makes a cursor, does the select with the data getting sorted appropriately, and passes the cursor back to JDBC.

What I'd like to know is, what is the best approach for accomplishing this? The result sets are potentially large so I really need the sorting to happen on the server side with the client just getting a cursor to the sorted result set.

Further detail:

Here is really what COMPUTE_KEY() will look like (pseudo code):

float compute_key(Row record)
{

	if (record.metric1 is not null)
		return(record.metric1);
	if (record.metric2 is not null)
		return(record.metric2);
	if (record.metric3) is not null)
		return(record.metric3);
	return(0);

So, can I do what I want with pure SQL? Should I use PL/SQL instead? Would a Java stored procedure perform as well as a PL/SQL procedure? Is there another clean solution you'd recommend (such as composing a temp table)?

Thanks a lot for your attention.

Scott

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 11 2000 - 17:32:31 CST