Re: Sorting query by a computed key.

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 12 Feb 2000 06:15:57 +0100
Message-ID: <950333025.14957.0.pluto.d4ee154e@news.demon.nl>

This can be resolved by a series of nested decodes

decode(record1.metric, NULL

                                  ,decode(record.metric2, NULL,
                                                decode(record.metric3, NULL,

0, record.metric3), record.metric2), record.metric1) Hth,

Sybrand Bakker, Oracle DBA

<silveys_at_my-deja.com> wrote in message news:88266f$1nf$1_at_nnrp1.deja.com...
> 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 - 23:15:57 CST