Slick Query
artm...@yahoo.com
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?
Dan Blum
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 to...@panix.com
"I wouldn't have believed it myself if I hadn't just made it up."
artm...@yahoo.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!
Maxim Demenko
And if you add a "desc" just after date_col, would Dan's solution not
solve your problem?
Best regards
Maxim
hpuxrac
It's always helpful if you note what version of oracle you are
running ... that way if people take the time to suggest a SQL query to
give you what you want ... it might actually work.
Certainly you can and should do it in 1 SQL statement but there are
various tricks that may not be applicable depending on version.
cc
<artm...@yahoo.com> wrote in message
news:f2ae60df-1cb2-4701...@v13g2000pro.googlegroups.com...
Yes. The SQL that follows uses the following database version:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Step 1: Using subquery factoring, create the set of test data (and show
that it can be generated)
SQL> with sample_data as (
2 select to_date('01/05/08', 'mm/dd/yy') as a_date, 'Complete' as a_status
from dual union
3 select to_date('02/15/09', 'mm/dd/yy'), 'Complete' from dual union
4 select to_date('02/18/08', 'mm/dd/yy'), 'Active' from dual union
5 select to_date('05/10/08', 'mm/dd/yy'), 'Cancelled' from dual union
6 select to_date('06/07/08', 'mm/dd/yy'), 'Pending' from dual union
7 select to_date('02/19/08', 'mm/dd/yy'), 'Complete' from dual
8 )
9 select * from sample_data
10 ;
A_DATE A_STATUS
----------- ---------
2008/01/05 Complete
2008/02/18 Active
2008/02/19 Complete
2008/05/10 Cancelled
2008/06/07 Pending
2009/02/15 Complete
6 rows selected
Step 2: Create a second named query, based on the first. The second named
query, called first_synthesis here, applies analytic functions. If any
record in the set has an active or pending state, the is_active_or_pending
column is set to 1, otherwise it is zero. The my_ordered_rownum column
orders the data in whatever ordering is specified in the row_number()
analytic funciton
SQL> with sample_data as (
2 select to_date('01/05/08', 'mm/dd/yy') as a_date, 'Complete' as a_status
from dual union
3 select to_date('02/15/09', 'mm/dd/yy'), 'Complete' from dual union
4 select to_date('02/18/08', 'mm/dd/yy'), 'Active' from dual union
5 select to_date('05/10/08', 'mm/dd/yy'), 'Cancelled' from dual union
6 select to_date('06/07/08', 'mm/dd/yy'), 'Pending' from dual union
7 select to_date('02/19/08', 'mm/dd/yy'), 'Complete' from dual
8 ),
9 first_synthesis as (
10 select a_date, a_status,
11 max (case a_status when 'Active' then 1
12 when 'Pending' then 1
13 else 0
14 end) over() is_active_or_pending,
15 row_number() over (order by a_date) my_ordered_rownum
16 from sample_data)
17 select * from first_synthesis
18
SQL> r
A_DATE A_STATUS IS_ACTIVE_OR_PENDING MY_ORDERED_ROWNUM
----------- --------- -------------------- -----------------
2008/01/05 Complete 1 1
2008/02/18 Active 1 2
2008/02/19 Complete 1 3
2008/05/10 Cancelled 1 4
2008/06/07 Pending 1 5
2009/02/15 Complete 1 6
Step 3: Pull everything together using two unioned queries which are
constructed such that at most one of the two will return data. This
implements the logiic that returns the first active or pending record when
one exists or the first complete or cancelled record when an active or
pending record does not exist. Please note that this solution is not
completely tested (no data found, case sensitive status information, etc),
but it is a start...
with sample_data as (
select to_date('01/05/08', 'mm/dd/yy') as a_date, 'Complete' as a_status
from dual union
select to_date('02/15/09', 'mm/dd/yy'), 'Complete' from dual union
select to_date('02/18/08', 'mm/dd/yy'), 'Active' from dual union
select to_date('05/10/08', 'mm/dd/yy'), 'Cancelled' from dual union
select to_date('06/07/08', 'mm/dd/yy'), 'Pending' from dual union
select to_date('02/19/08', 'mm/dd/yy'), 'Complete' from dual
),
first_synthesis as (
select a_date, a_status,
max (case a_status when 'Active' then 1
when 'Pending' then 1
else 0
end) over() is_active_or_pending,
row_number() over (order by a_date) my_ordered_rownum
from sample_data)
select a_date, a_status
from first_synthesis
where is_active_or_pending = 1
and my_ordered_rownum =
(select min(my_ordered_rownum)
from first_synthesis
where a_status in ('Active','Pending'))
union
select a_date, a_status
from first_synthesis
where is_active_or_pending = 0
and my_ordered_rownum =
(select min(my_ordered_rownum)
from first_synthesis
where a_status in ('Complete','Cancelled') )
A_DATE A_STATUS
----------- ---------
2008/02/18 Active