Re: Slick Query

artmerar_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					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."