Re: Slick Query

artmerar_at_yahoo.com schrieb:
> 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 -

And if you add a "desc" just after date_col, would Dan's solution not solve your problem?

Best regards

Maxim Received on Fri Jul 25 2008 - 14:16:25 CDT