Re: Slick Query

On Jul 25, 1:14 pm, t..._at_panix.com (Dan Blum) wrote:
> artme..._at_yahoo.com wrote:
> > I want to see if I can create a slick query to do this and cut down on
> > some code.
> > I have a list of dates & statuses like this:
> > 01/05/08 Complete
> > 02/15/09 Complete
> > 02/18/08 Active
> > 05/10/08 Cancelled
> > 06/07/08 Pending
> > 02/19/08 Complete
> > I'll order them in the query.  What I need to do is first check to see
> > if there is a status of Active or Pending and return the first one I
> > encounter.  If those do not exist, I need to return the first Complete
> > or Cancelled I encounter.
> > In this case Active would be returned.
> > Right now I have a cursor and some PL/SQL going through 2 loops
> > checking for Active/Pending, then Complete/Cancelled.  I'd like to see
> > if I can do it all in one query.
> > Any hopes of doing this?
>
> What do you mean by the "first one I encounter?" The records you show
> are not in any kind of order. If you mean the EARLIEST, then you can
> do it like so (there are other ways, too):
>
> select * from
> (select date_col, status
>    from foo
>  order by decode(status, 'Active', 1, 'Pending', 1, 2), date_col)
> where rownum = 1;
>
> If that is not what you mean, you need to explain the problem in more detail.
>
> --
> _______________________________________________________________________
> Dan Blum                                                 t..._at_panix.com        
> "I wouldn't have believed it myself if I hadn't just made it up."- Hide quoted text -
>
> - Show quoted text -

Hi Dan, thanks for replying.

I will order the records in decending order when they are selected. Then, if an Active or Pending status exists in the result set, return the first one that is encountered. Otherwise return the first Complete or Cancelled status encountered.

01/05/08 Complete
02/15/09 Complete
02/18/08 Active
05/10/08 Cancelled
06/07/08 Pending
02/19/08 Complete

In this case, once ordered, I'd encounter the Active first and return that.

02/15/09 Complete
05/10/08 Cancelled
01/05/08 Complete
02/19/08 Complete

In this case, since there is no Active or Pending, I'll return the Complete on 1/5/08.

Again, I'll oredr the dates decending......

Thanks Dan! Received on Fri Jul 25 2008 - 13:21:06 CDT