Re: Slick Query

<artmerar_at_yahoo.com> wrote in message
news:f2ae60df-1cb2-4701-a278-e1cd85ad1018_at_v13g2000pro.googlegroups.com...
>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?
>

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 Received on Mon Jul 28 2008 - 07:59:39 CDT