Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

match_recognize: Working with pattern-matching for multiple columns

mNemMay 10 2018 — edited May 15 2018

Trying to work out a solution for a recently posted question (edit: ).

For columns v1 and v2 in each row, need to find the sum of all values occurring in the subsequent rows

upto a value that is 100 or greater.

with t(id, v1, v2) as

(

  select 1,  80 ,  20 from dual union all

  select 2,  90 ,  40 from dual union all

  select 3, 130 , 170 from dual union all

  select 4,  20 ,  30 from dual union all

  select 5, 150 , 140 from dual union all

  select 6,  30 ,  40 from dual union all

  select 7, 110 , 110 from dual union all

  select 8,  20 , 220 from dual union all

  select 9, 130 ,  30 from dual

)

select * from t;

expected resultset:

        ID     V1FRST      V1SUM     V2FRST      V2SUM

---------- ---------- ---------- ---------- ----------

         1         80        300         20        230

         2         90        220         40        210

         3        130        300        170        340

         4         20        170         30        170

         5        150        290        140        290

         6         30        140         40        150

         7        110        260        110        330

         8         20        150        220        250

         9        130        130         30         30

Thanks.

This post has been answered by mathguy on May 10 2018
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 12 2018
Added on May 10 2018
36 comments
1,898 views