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!

Handle negative and corresponding positive records..

GVRNov 1 2011 — edited Nov 3 2011
I need some help in achieving a efficient way(We might run this on a table with 1+ billion records) to handle positive and negative values as explained below.
Basically *"if set of records has a negative value then i need to exclude those negative values and also exclude corresponding positive values(it could be exact positive value or sum of positive values)"*

Oracle Version
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Example
with temp_data as
 (select 1 seq, 'A' ident, -101 value  from dual  union all
  select 2, 'A', 100    from dual  union all
  select 3, 'A', 200    from dual  union all
  select 4, 'A', 101    from dual  union all
  select 5, 'B', 100    from dual  union all
  select 6, 'C', -100    from dual  union all
  select 7, 'D', -100    from dual  union all
  select 8, 'D', 101    from dual  union all
  select 9, 'D', 99    from dual  union all
  select 10, 'D', 1 from dual union all
  select 11, 'E', 1 from dual union all
  select 12, 'E', 2 from dual union all
  select 13, 'E', 3 from dual union all
  select 14, 'E', -10 from dual union all
  select 15, 'E', 7 from dual union all
  select 16, 'E', 8 from dual
  )
select * from temp_data;
set of records are identified with column ident


For Ident ='A' exclude seq 1,4
For Ident ='B' no exclusions
For Ident ='C' exclude 6
For Ident ='D' exclude 7,(9 & 10)
For Ident ='E' exclude 14,(13 & 15) or (12 & 16)

Note: For Ident ='E' only one set of positive values needs to be excluded.
Final output should look like

Either

seq          ident       value
2            A           100
3            A           200
5            B           100
8            D           101
11           E           1
12           E           2
16           E           8
OR
seq          ident       value
2            A           100
3            A           200
5            B           100
8            D           101
11           E           1
13           E           2
15           E           8
Any suggestions would be highly appreciated.
This post has been answered by Frank Kulash on Nov 1 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 1 2011
Added on Nov 1 2011
6 comments
1,471 views