Re: Slick Query
Date: Fri, 25 Jul 2008 11:21:06 -0700 (PDT)
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