Re: Sorting query by a computed key.

Sybrand Bakker wrote:
>
> 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.

Or?

 ...order by

      column1, 
      column2, 
      NVL(record.metric1,NVL(record.metric2,NVL(record.metric3,0))), 
      column3 ...
  

Yours,

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Sat Feb 12 2000 - 01:53:25 CST