This discussion is archived
9 Replies Latest reply: Mar 4, 2013 3:53 AM by jeneesh RSS

Querying on data sections defined by the data itself

cubmar Newbie
Currently Being Moderated
How to return control range of table to query.

Please consider the following data (created via the following):
create table mc_acttest as
(
select 'Activity 1' id, 'N' throttle from dual UNION ALL
select 'Activity 2' id, 'Y' throttle from dual UNION ALL
select 'Activity 3' id, 'Y' throttle from dual UNION ALL
select 'Activity 4' id, 'N' throttle from dual UNION ALL
select 'Activity 5' id, 'N' throttle from dual UNION ALL
select 'Activity 6' id, 'N' throttle from dual UNION ALL
select 'Activity 7' id, 'Y' throttle from dual UNION ALL
select 'Activity 8' id, 'N' throttle from dual UNION ALL
select 'Activity 9' id, 'Y' throttle from dual UNION ALL
select 'Activity 10' id, 'Y' throttle from dual UNION ALL
select 'Activity 11' id, 'N' throttle from dual UNION ALL
select 'Activity 12' id, 'N' throttle from dual UNION ALL
select 'Activity 13' id, 'Y' throttle from dual UNION ALL
select 'Activity 14' id, 'N' throttle from dual UNION ALL
select 'Activity 15' id, 'N' throttle from dual UNION ALL
select 'Activity 16' id, 'Y' throttle from dual UNION ALL
select 'Activity 17' id, 'Y' throttle from dual UNION ALL
select 'Activity 18' id, 'Y' throttle from dual UNION ALL
select 'Activity 19' id, 'N' throttle from dual UNION ALL
select 'Activity 20' id, 'Y' throttle from dual UNION ALL
select 'Activity 21' id, 'N' throttle from dual UNION ALL
select 'Activity 22' id, 'N' throttle from dual UNION ALL
select 'Activity 23' id, 'Y' throttle from dual UNION ALL
select 'Activity 24' id, 'Y' throttle from dual UNION ALL
select 'Activity 25' id, 'N' throttle from dual 
)


create table mc_rptest as
(
select 0010 routepos, 'START ROUTE' what from dual UNION ALL 
select 0020 routepos, 'Delay 3 days' what from dual UNION ALL 
select 0030 routepos, 'Delay 4 days' what from dual UNION ALL 
select 0040 routepos, 'Activity 1' what from dual UNION ALL 
select 0050 routepos, 'Delay 2 days' what from dual UNION ALL 
select 0060 routepos, 'Activity 2' what from dual UNION ALL 
select 0070 routepos, 'Delay 3 days' what from dual UNION ALL 
select 0080 routepos, 'Delay 2 days' what from dual UNION ALL 
select 0090 routepos, 'Activity 3' what from dual UNION ALL 
select 0100 routepos, 'Activity 4' what from dual UNION ALL 
select 0110 routepos, 'Delay 2 days' what from dual UNION ALL 
select 0120 routepos, 'Descision 1' what from dual UNION ALL 
select 0130 routepos, 'Activity 5' what from dual UNION ALL 
select 0140 routepos, 'Descision 2' what from dual UNION ALL 
select 0150 routepos, 'Activity 6' what from dual UNION ALL 
select 0160 routepos, 'Activity 7' what from dual UNION ALL 
select 0170 routepos, 'Activity 25' what from dual UNION ALL 
select 0180 routepos, 'Delay 2 days' what from dual UNION ALL 
select 0190 routepos, 'Delay 1 day' what from dual UNION ALL 
select 0200 routepos, 'Activity 8' what from dual UNION ALL 
select 0210 routepos, 'Delay 4 days' what from dual UNION ALL 
select 0220 routepos, 'Delay 2 days' what from dual UNION ALL 
select 0230 routepos, 'Delay 1 day' what from dual UNION ALL 
select 0240 routepos, 'Descision 3' what from dual UNION ALL 
select 0250 routepos, 'Activity 9' what from dual UNION ALL 
select 0260 routepos, 'Activity 10' what from dual UNION ALL 
select 0270 routepos, 'Descision 4' what from dual UNION ALL 
select 0280 routepos, 'Activity 11' what from dual UNION ALL 
select 0290 routepos, 'Delay 1 day' what from dual UNION ALL 
select 0300 routepos, 'Activity 12' what from dual UNION ALL 
select 0310 routepos, 'Delay 3 days' what from dual UNION ALL 
select 0320 routepos, 'Delay 4 days' what from dual UNION ALL 
select 0330 routepos, 'Activity 13' what from dual UNION ALL 
select 0340 routepos, 'Delay 2 days' what from dual UNION ALL 
select 0350 routepos, 'Activity 14' what from dual UNION ALL 
select 0360 routepos, 'Delay 3 days' what from dual UNION ALL 
select 0370 routepos, 'Delay 2 days' what from dual UNION ALL 
select 0380 routepos, 'Activity 15' what from dual UNION ALL 
select 0390 routepos, 'Activity 16' what from dual UNION ALL 
select 0400 routepos, 'Delay 2 days' what from dual UNION ALL 
select 0410 routepos, 'Descision 5' what from dual UNION ALL 
select 0420 routepos, 'Activity 17' what from dual UNION ALL 
select 0430 routepos, 'Descision 6' what from dual UNION ALL 
select 0440 routepos, 'Activity 18' what from dual UNION ALL 
select 0450 routepos, 'Activity 19' what from dual UNION ALL 
select 0460 routepos, 'Activity 24' what from dual UNION ALL 
select 0470 routepos, 'Delay 2 days' what from dual UNION ALL 
select 0480 routepos, 'Delay 1 day' what from dual UNION ALL 
select 0490 routepos, 'Activity 20' what from dual UNION ALL 
select 0500 routepos, 'Delay 4 days' what from dual UNION ALL 
select 0510 routepos, 'Delay 2 days' what from dual UNION ALL 
select 0520 routepos, 'Delay 1 day' what from dual UNION ALL 
select 0530 routepos, 'Descision 7' what from dual UNION ALL 
select 0540 routepos, 'Activity 21' what from dual UNION ALL 
select 0550 routepos, 'Activity 22' what from dual UNION ALL 
select 0560 routepos, 'Descision 8' what from dual UNION ALL 
select 0570 routepos, 'Activity 23' what from dual UNION ALL 
select 0580 routepos, 'Delay 1 day' what from dual UNION ALL 
select 0590 routepos, 'Activity 23' what from dual
)
The route pos is a position in the table. So routepos 10 is the very start, 590 is the very end. Decisions can move to anywhere else otherwise things progress upwards (ie 10 .. 20... 30 etc)

There is an accounts table and an account has a route pos (which tells us where an account is):
create table mc_acctest as
(
select 'Acc 1' accid, 0010 routepos from dual UNION ALL
select 'Acc 2' accid, 0020 routepos from dual UNION ALL
select 'Acc 3' accid, 0030 routepos from dual UNION ALL
select 'Acc 4' accid, 0040 routepos from dual UNION ALL
select 'Acc 5' accid, 0050 routepos from dual UNION ALL
select 'Acc 6' accid, 0060 routepos from dual UNION ALL
select 'Acc 7' accid, 0070 routepos from dual UNION ALL
select 'Acc 8' accid, 0080 routepos from dual UNION ALL
select 'Acc 9' accid, 0090 routepos from dual UNION ALL
select 'Acc 10' accid, 0100 routepos from dual UNION ALL
select 'Acc 11' accid, 0110 routepos from dual UNION ALL
select 'Acc 12' accid, 0120 routepos from dual UNION ALL
select 'Acc 13' accid, 0130 routepos from dual UNION ALL
select 'Acc 14' accid, 0140 routepos from dual UNION ALL
select 'Acc 15' accid, 0150 routepos from dual UNION ALL
select 'Acc 16' accid, 0160 routepos from dual UNION ALL
select 'Acc 17' accid, 0170 routepos from dual UNION ALL
select 'Acc 18' accid, 0180 routepos from dual UNION ALL
select 'Acc 19' accid, 0190 routepos from dual UNION ALL
select 'Acc 20' accid, 0200 routepos from dual UNION ALL
select 'Acc 21' accid, 0210 routepos from dual UNION ALL
select 'Acc 22' accid, 0220 routepos from dual UNION ALL
select 'Acc 23' accid, 0230 routepos from dual UNION ALL
select 'Acc 24' accid, 0240 routepos from dual UNION ALL
select 'Acc 25' accid, 0250 routepos from dual UNION ALL
select 'Acc 26' accid, 0260 routepos from dual UNION ALL
select 'Acc 27' accid, 0270 routepos from dual UNION ALL
select 'Acc 28' accid, 0280 routepos from dual UNION ALL
select 'Acc 29' accid, 0290 routepos from dual UNION ALL
select 'Acc 30' accid, 0300 routepos from dual UNION ALL
select 'Acc 31' accid, 0310 routepos from dual UNION ALL
select 'Acc 32' accid, 0320 routepos from dual UNION ALL
select 'Acc 33' accid, 0330 routepos from dual UNION ALL
select 'Acc 34' accid, 0340 routepos from dual UNION ALL
select 'Acc 35' accid, 0350 routepos from dual UNION ALL
select 'Acc 36' accid, 0360 routepos from dual UNION ALL
select 'Acc 37' accid, 0370 routepos from dual UNION ALL
select 'Acc 38' accid, 0380 routepos from dual UNION ALL
select 'Acc 39' accid, 0390 routepos from dual UNION ALL
select 'Acc 40' accid, 0400 routepos from dual UNION ALL
select 'Acc 41' accid, 0410 routepos from dual UNION ALL
select 'Acc 42' accid, 0420 routepos from dual UNION ALL
select 'Acc 43' accid, 0430 routepos from dual UNION ALL
select 'Acc 44' accid, 0440 routepos from dual UNION ALL
select 'Acc 45' accid, 0450 routepos from dual UNION ALL
select 'Acc 46' accid, 0460 routepos from dual UNION ALL
select 'Acc 47' accid, 0470 routepos from dual UNION ALL
select 'Acc 48' accid, 0480 routepos from dual UNION ALL
select 'Acc 49' accid, 0490 routepos from dual UNION ALL
select 'Acc 50' accid, 0500 routepos from dual UNION ALL
select 'Acc 51' accid, 0510 routepos from dual UNION ALL
select 'Acc 52' accid, 0520 routepos from dual UNION ALL
select 'Acc 53' accid, 0530 routepos from dual UNION ALL
select 'Acc 54' accid, 0540 routepos from dual UNION ALL
select 'Acc 55' accid, 0550 routepos from dual UNION ALL
select 'Acc 56' accid, 0560 routepos from dual UNION ALL
select 'Acc 57' accid, 0570 routepos from dual UNION ALL
select 'Acc 58' accid, 0580 routepos from dual UNION ALL
select 'Acc 59' accid, 0590 routepos from dual UNION ALL
select 'Acc 60' accid, 0600 routepos from dual
)
Putting it all together, we get this output:
select
*
from
mc_acctest acc
left join
mc_rptest rp
on acc.routepos=rp.ROUTEPOS
left join
mc_acttest act
on rp.WHAT=act.id
order by
acc.routepos
ACCID  ROUTEPOS ROUTEPOS WHAT         ID          THROTTLE
------ -------- -------- ------------ ----------- --------
Acc 1        10       10 START ROUTE                      
Acc 2        20       20 Delay 3 days                     
Acc 3        30       30 Delay 4 days                     
Acc 4        40       40 Activity 1   Activity 1  N       
Acc 5        50       50 Delay 2 days                     
Acc 6        60       60 Activity 2   Activity 2  Y       
Acc 7        70       70 Delay 3 days                     
Acc 8        80       80 Delay 2 days                     
Acc 9        90       90 Activity 3   Activity 3  Y       
Acc 10      100      100 Activity 4   Activity 4  N       
Acc 11      110      110 Delay 2 days                     
Acc 12      120      120 Descision 1                      
Acc 13      130      130 Activity 5   Activity 5  N       
Acc 14      140      140 Descision 2                      
Acc 15      150      150 Activity 6   Activity 6  N       
Acc 16      160      160 Activity 7   Activity 7  Y       
Acc 17      170      170 Activity 25  Activity 25 N       
Acc 18      180      180 Delay 2 days                     
Acc 19      190      190 Delay 1 day                      
Acc 20      200      200 Activity 8   Activity 8  N       
Acc 21      210      210 Delay 4 days                     
Acc 22      220      220 Delay 2 days                     
Acc 23      230      230 Delay 1 day                      
Acc 24      240      240 Descision 3                      
Acc 25      250      250 Activity 9   Activity 9  Y       
Acc 26      260      260 Activity 10  Activity 10 Y       
Acc 27      270      270 Descision 4                      
Acc 28      280      280 Activity 11  Activity 11 N       
Acc 29      290      290 Delay 1 day                      
Acc 30      300      300 Activity 12  Activity 12 N       
Acc 31      310      310 Delay 3 days                     
Acc 32      320      320 Delay 4 days                     
Acc 33      330      330 Activity 13  Activity 13 Y       
Acc 34      340      340 Delay 2 days                     
Acc 35      350      350 Activity 14  Activity 14 N       
Acc 36      360      360 Delay 3 days                     
Acc 37      370      370 Delay 2 days                     
Acc 38      380      380 Activity 15  Activity 15 N       
Acc 39      390      390 Activity 16  Activity 16 Y       
Acc 40      400      400 Delay 2 days                     
Acc 41      410      410 Descision 5                      
Acc 42      420      420 Activity 17  Activity 17 Y       
Acc 43      430      430 Descision 6                      
Acc 44      440      440 Activity 18  Activity 18 Y       
Acc 45      450      450 Activity 19  Activity 19 N       
Acc 46      460      460 Activity 24  Activity 24 Y       
Acc 47      470      470 Delay 2 days                     
Acc 48      480      480 Delay 1 day                      
Acc 49      490      490 Activity 20  Activity 20 Y       
Acc 50      500      500 Delay 4 days                     
Acc 51      510      510 Delay 2 days                     
Acc 52      520      520 Delay 1 day                      
Acc 53      530      530 Descision 7                      
Acc 54      540      540 Activity 21  Activity 21 N       
Acc 55      550      550 Activity 22  Activity 22 N       
Acc 56      560      560 Descision 8                      
Acc 57      570      570 Activity 23  Activity 23 Y       
Acc 58      580      580 Delay 1 day                      
Acc 59      590      590 Activity 23  Activity 23 Y       
Acc 60      600                                           
60 rows selected
 
The objective is to select only accounts that meet certain criteria...

If the account's route pos matches a 'what' that is an activity and that activity has a throttle value of Y, it should be included. [because the CURRENT item is matched)

If the account's route pos matrches a 'what' that is a descision, it should be excluded. (because as a descision is to be made, we have no idea on the outcome and eventual position)

Otherwise, regardless of the current position, any 'whats' between the current position and the next DELAY, DESCISION or end of table should be reveiwed and if any position has an activity with Throttle = Y then it should be included.


EG:
Acc 40 would be excluded
Acc 42 would be included
Acc 41 would be excluded
Acc 48 would be included
Acc 19 would be excluded
Acc 45 would be included
Acc 49 would be included

This is over Oracle 9i.

Many thanks for any ideas how to achieve this in an efficient manner.  I can post expected results if it helps!

Thanks                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 1. Re: Querying on data sections defined by the data itself
    jeneesh Guru
    Currently Being Moderated
    cubmar wrote:
    The objective is to select only accounts that meet certain criteria...

    If the account's route pos matches a 'what' that is an activity
    Do you mean "what like 'Activity%'" ?
    and that activity has a throttle value of Y, it should be included. [because the CURRENT item is matched)

    If the account's route pos matrches a 'what' that is a descision, it should be excluded. (because as a descision is to be made, we have no idea on the outcome and eventual position)

    Otherwise, regardless of the current position, any 'whats' between the current position and the next DELAY, DESCISION or end of table should be reveiwed and if any position has an activity with Throttle = Y then it should be included. Not clear..> > > EG:> Acc 40 would be excluded> Acc 42 would be included> Acc 41 would be excluded> Acc 48 would be included> Acc 19 would be excluded> Acc 45 would be includedWhy 45 should be included?> Acc 49 would be included> > This is over Oracle 9i.> > Many thanks for any ideas how to achieve this in an efficient manner.  I can post expected results if it helps!> That will help us to help you..                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 2. Re: Querying on data sections defined by the data itself
    cubmar Newbie
    Currently Being Moderated
    Expected:
    pos     current                  meaning             searchuntil...      Throt act count     Outcome
    020     Delay                    Search deeper       30                  0                   Exclude from query  
    030     Delay                    Search deeper       50                  0                   Exclude from query  
    040     Non-Throttled Activity   Search deeper       50                  0                   Exclude from query  
    050     Delay                    Search deeper       70                  1                   Include in query    
    060     Throttled Activity       Throttle                                                    Include in query    
    070     Delay                    Search deeper       80                  0                   Exclude from query  
    080     Delay                    Search deeper       110                 1                   Include in query    
    090     Throttled Activity       Throttle                                                    Include in query    
    100     Non-Throttled Activity   Search deeper       110                 0                   Exclude from query  
    110     Delay                    Search deeper       120                 0                   Exclude from query  
    120     Decision                 Do not throttle                                             Exclude from query  
    130     Non-Throttled Activity   Search deeper       140                 0                   Exclude from query  
    140     Decision                 Do not throttle                                             Exclude from query  
    150     Non-Throttled Activity   Search deeper       180                 1                   Include in query    
    160     Throttled Activity       Throttle                                                    Include in query    
    170     Non-Throttled Activity   Search deeper       180                 0                   Exclude from query  
    180     Delay                    Search deeper       190                 0                   Exclude from query  
    190     Delay                    Search deeper       210                 0                   Exclude from query  
    200     Non-Throttled Activity   Search deeper       210                 0                   Exclude from query  
    210     Delay                    Search deeper       220                 0                   Exclude from query  
    220     Delay                    Search deeper       230                 0                   Exclude from query  
    230     Delay                    Search deeper       240                 0                   Exclude from query  
    240     Decision                 Do not throttle                                             Exclude from query  
    250     Throttled Activity       Throttle                                                    Include in query    
    260     Throttled Activity       Throttle                                                    Include in query    
    270     Decision                 Do not throttle                                             Exclude from query  
    280     Non-Throttled Activity   Search deeper       290                 0                   Exclude from query  
    290     Delay                    Search deeper       310                 0                   Exclude from query  
    300     Non-Throttled Activity   Search deeper       310                 0                   Exclude from query  
    310     Delay                    Search deeper       320                 0                   Exclude from query  
    320     Delay                    Search deeper       340                 1                   Include in query    
    330     Throttled Activity       Throttle                                                    Include in query    
    340     Delay                    Search deeper       360                 0                   Exclude from query  
    350     Non-Throttled Activity   Search deeper       360                 0                   Exclude from query  
    360     Delay                    Search deeper       370                 0                   Exclude from query  
    370     Delay                    Search deeper       400                 1                   Include in query    
    380     Non-Throttled Activity   Search deeper       400                 1                   Include in query    
    390     Throttled Activity       Throttle                                                    Include in query    
    400     Delay                    Search deeper       410                 0                   Exclude from query  
    410     Decision                 Do not throttle                                             Exclude from query  
    420     Throttled Activity       Throttle                                                    Include in query    
    430     Decision                 Do not throttle                                             Exclude from query  
    440     Throttled Activity       Throttle                                                    Include in query    
    450     Non-Throttled Activity   Search deeper       470                 1                   Include in query    
    460     Throttled Activity       Throttle                                                    Include in query    
    470     Delay                    Search deeper       480                 0                   Exclude from query  
    480     Delay                    Search deeper       500                 1                   Include in query    
    490     Throttled Activity       Throttle                                                    Include in query    
    500     Delay                    Search deeper       510                 0                   Exclude from query  
    510     Delay                    Search deeper       520                 0                   Exclude from query  
    520     Delay                    Search deeper       530                 0                   Exclude from query  
    530     Decision                 Do not throttle                                             Exclude from query  
    540     Non-Throttled Activity   Search deeper       560                 0                   Exclude from query  
    550     Non-Throttled Activity   Search deeper       560                 0                   Exclude from query  
    560     Decision                 Do not throttle                                             Exclude from query  
    570     Throttled Activity       Throttle                                                    Include in query    
    580     Delay                    Search deeper       590 (end of route)  1                   Include in query    
    590     Throttled Activity       Throttle                                                    Include in query    
  • 3. Re: Querying on data sections defined by the data itself
    cubmar Newbie
    Currently Being Moderated
    Many thanks for your response.

    Yes, Activity% would be the check, thanks.

    Acc 45 would be included because although it is a non-throttle activity, the next one is (and there is no delay or decision between them which would have ended the search)

    Basically from the starting (current) pos you need to check each pos until DESCSION/DELAY/EOF is reached... and if there are ANY activity with Throttle = Y then you include it.
  • 4. Re: Querying on data sections defined by the data itself
    jeneesh Guru
    Currently Being Moderated
    Like..
    select accid,routepos,what,id,throttle
    from(
        select acc.accid,acc.routepos,rp.what,act.id,act.throttle,
               lead(rp.what) over(order by acc.routepos) next_what,
               lead(act.throttle) over(order by acc.routepos) next_throttle
        from mc_acctest acc
        left join mc_rptest rp
          on acc.routepos=rp.routepos
        left join mc_acttest act
          on rp.what=act.id
        )
    where
      (
       what like 'Activity%'  
           and throttle = 'Y'
      )
      or
      ( 
        what not like 'Descision%'  
        and
             (
              next_what like 'Activity%'  
              and next_throttle = 'Y'    
             )
       ) 
    order by routepos;
    
    ACCID  ROUTEPOS WHAT         ID          THROTTLE
    ------ -------- ------------ ----------- --------
    Acc 5        50 Delay 2 days                      
    Acc 6        60 Activity 2   Activity 2  Y        
    Acc 8        80 Delay 2 days                      
    Acc 9        90 Activity 3   Activity 3  Y        
    Acc 15      150 Activity 6   Activity 6  N        
    Acc 16      160 Activity 7   Activity 7  Y        
    Acc 25      250 Activity 9   Activity 9  Y        
    Acc 26      260 Activity 10  Activity 10 Y        
    Acc 32      320 Delay 4 days                      
    Acc 33      330 Activity 13  Activity 13 Y        
    Acc 38      380 Activity 15  Activity 15 N        
    Acc 39      390 Activity 16  Activity 16 Y        
    Acc 42      420 Activity 17  Activity 17 Y        
    Acc 44      440 Activity 18  Activity 18 Y        
    Acc 45      450 Activity 19  Activity 19 N        
    Acc 46      460 Activity 24  Activity 24 Y        
    Acc 48      480 Delay 1 day                       
    Acc 49      490 Activity 20  Activity 20 Y        
    Acc 57      570 Activity 23  Activity 23 Y        
    Acc 58      580 Delay 1 day                       
    Acc 59      590 Activity 23  Activity 23 Y        
    
     21 rows selected 
  • 5. Re: Querying on data sections defined by the data itself
    cubmar Newbie
    Currently Being Moderated
    WOW!! Extremely close but no cigar... not yet anyway....

    Missing is Acc 37. This is on pos 370 which is a delay.. so it should search 380 which is non throttle activity so carry on to 390 which is Throttle activity. Should be included.

    Thanks
  • 6. Re: Querying on data sections defined by the data itself
    jeneesh Guru
    Currently Being Moderated
    select accid,routepos,what,id,throttle
    from(
            select accid,routepos,what,id,throttle,
                   last_value(
                      case when next_throttle='Y' or next_what not like 'Activity%' then next_what
                           else null
                      end ignore nulls)
                       over(order by routepos desc ) next_what
            from(
                  select acc.accid,acc.routepos,rp.what,act.id,act.throttle,
                         lead(rp.what) over(order by acc.routepos) next_what,
                         lead(act.throttle) over(order by acc.routepos) next_throttle          
                  from mc_acctest acc
                  left join mc_rptest rp
                    on acc.routepos=rp.routepos
                  left join mc_acttest act
                    on rp.what=act.id
                )
         )
    where
      (
       what like 'Activity%'  
           and throttle = 'Y'
      )
      or
      ( 
        what not like 'Descision%'  
        and next_what like 'Activity%'  
       ) 
    order by routepos;
    
    ACCID  ROUTEPOS WHAT         ID          THROTTLE
    ------ -------- ------------ ----------- --------
    Acc 5        50 Delay 2 days                      
    Acc 6        60 Activity 2   Activity 2  Y        
    Acc 8        80 Delay 2 days                      
    Acc 9        90 Activity 3   Activity 3  Y        
    Acc 15      150 Activity 6   Activity 6  N        
    Acc 16      160 Activity 7   Activity 7  Y        
    Acc 25      250 Activity 9   Activity 9  Y        
    Acc 26      260 Activity 10  Activity 10 Y        
    Acc 32      320 Delay 4 days                      
    Acc 33      330 Activity 13  Activity 13 Y        
    Acc 37      370 Delay 2 days                      
    Acc 38      380 Activity 15  Activity 15 N        
    Acc 39      390 Activity 16  Activity 16 Y        
    Acc 42      420 Activity 17  Activity 17 Y        
    Acc 44      440 Activity 18  Activity 18 Y        
    Acc 45      450 Activity 19  Activity 19 N        
    Acc 46      460 Activity 24  Activity 24 Y        
    Acc 48      480 Delay 1 day                       
    Acc 49      490 Activity 20  Activity 20 Y        
    Acc 57      570 Activity 23  Activity 23 Y        
    Acc 58      580 Delay 1 day                       
    Acc 59      590 Activity 23  Activity 23 Y        
    
     22 rows selected 
  • 7. Re: Querying on data sections defined by the data itself
    cubmar Newbie
    Currently Being Moderated
    That looks brilliant, thank you!

    Can I confirm, before I get a chance to test it, if there are 5 non-throttle activities and the 6th is a throttle one, it will still include the account. I.e. its looking at all levels up to the 1st decision/delay/eof?

    Also, I get an error:
    select accid,routepos,what,id,throttle
    from(
    select accid,routepos,what,id,throttle,
    last_value(
    case when next_throttle='Y' or next_what not like 'Activity%'
    else null
    end ignore nulls)
    over(order by routepos desc ) next_what
    from(
    select acc.accid,acc.routepos,rp.what,act.id,act.throttle,
    lead(rp.what) over(order by acc.routepos) next_what,
    lead(act.throttle) over(order by acc.routepos) next_throttl
    from mc_acctest acc
    left join mc_rptest rp
    on acc.routepos=rp.routepos
    left join mc_acttest act
    on rp.what=act.id
    )
    )
    where
    (
    what like 'Activity%'
    and throttle = 'Y'
    )
    or
    (
    what not like 'Descision%'
    and next_what like 'Activity%'
    )
    order by routepos
    ORA-00907: missing right parenthesis


    The cursor ends up on the highlighted ignore in ignore nulls.


    If i comment it out from
    end ignore nulls)
    to
    end )--ignore nulls)

    it works, but only 21 rows.

    Is it an Oracle 9i thing?
  • 8. Re: Querying on data sections defined by the data itself
    cubmar Newbie
    Currently Being Moderated
    i.e.

    if you do this:
    update mc_acttest set throttle='N' where id='Activity 18'
    commit;
    does acct 44 still get included?
  • 9. Re: Querying on data sections defined by the data itself
    jeneesh Guru
    Currently Being Moderated
    cubmar wrote:
    i.e.

    if you do this:
    update mc_acttest set throttle='N' where id='Activity 18'
    commit;
    does acct 44 still get included?
    Sure, because the next record to be considered is Acc 46 - which has throttle = 'Y'

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points