9 Replies Latest reply: Mar 4, 2013 5:53 AM by jeneesh RSS

    Querying on data sections defined by the data itself

    cubmar
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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'