 3,734,027 Users
 2,246,861 Discussions
 7,857,001 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
 380.9K All Categories
 2.1K Data
 203 Big Data Appliance
 1.9K Data Science
 446.1K Databases
 220.4K General Database Discussions
 3.7K Java and JavaScript in the Database
 23 Multilingual Engine
 506 MySQL Community Space
 459 NoSQL Database
 7.7K Oracle Database Express Edition (XE)
 2.8K ORDS, SODA & JSON in the Database
 438 SQLcl
 3.9K SQL Developer Data Modeler
 185.4K SQL & PL/SQL
 20.8K SQL Developer
 291.3K Development
 6 Developer Projects
 117 Programming Languages
 288K Development Tools
 96 DevOps
 3K QA/Testing
 645.2K Java
 18 Java Learning Subscription
 36.9K Database Connectivity
 148 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.7K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 12 Java Essentials
 138 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 195 Java User Groups
 22 JavaScript  Nashorn
 Programs
 179 LiveLabs
 34 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.7K Other Languages
 2.3K Chinese
 165 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 225 Portuguese
Generating column based on sparsed values
548091
Member Posts: 103
Hi,
I have the following data in Prod table:
Any hints/solutions on how to generate the above Result column would be appreciated.
Thanks in advance,
I have the following data in Prod table:
WK_NUM Prod Qty    1 A 0 2 A 10 3 A 20 4 A 15 5 A 20 6 A 10 7 A 5 8 A 50 9 A 15 10 A 0 11 A 10 12 A 30 13 A 40 14 A 100 15 A 20 16 A 40 17 A 35 18 A 20 19 A 10I need to generate the below Result column:
WK_NUM Prod Qty Result Comment on how to generate the Result column      1 A 0 0 Value is zero, so copy as is 2 A 10 10 Value is positive, so copy as is 3 A 20 20 Same 4 A 15 0 Negative so make zero, Keep it zero till the point where the total does not become positive again 5 A 20 0 6 A 10 0 7 A 5 0 8 A 50 30 Addition of values from WK_NUM=4 to WK_NUM=8 becomes positive again 9 A 15 15 Value is positive, so copy as is 10 A 0 0 11 A 10 0 Negative so make zero, Keep it zero till the point where the total does not become positive again 12 A 30 0 13 A 40 0 14 A 100 20 Addition of values from WK_NUM=11 to WK_NUM=14 becomes positive again 15 A 20 0 Negative so make zero, Keep it zero till the point where the total does not become positive again 16 A 40 20 Addition of values from WK_NUM=15 to WK_NUM=16 becomes positive again 17 A 35 35 Value is positive, so copy as is 18 A 20 20 Value is positive, so copy as is 19 A 10 10 Value is positive, so copy as isHope I have been clear in providing the correct details.
Any hints/solutions on how to generate the above Result column would be appreciated.
Thanks in advance,
Answers

Why did you miss values for week_num 6 & 7 ? The result is 0 whereas i was expecting 5 & 10. Could you also post some create and insert scripts of the table ?

Hi,
Apologies for belated reply.
There's no missing values for wk_num 6 & 7.
Below is the part of the data as to how the 0 appears for negative values. The "Logical Running Sum" is not a physical column, but I am giving it below just for understanding, and using this logical column the Result column is having 0 for negative values.WK_NUM Product Qty Logical Running Sum Result 4 A 15 15 0 5 A 20 35 0 6 A 10 25 0 7 A 5 20 0 8 A 50 30 30
Same way it works for wk_num's 11 to 14 and wk_num's 15 to 16.
Looking forward to any solutions.
Thanks, 
Whenever you post any problem please post the DDL & DML for that environment. Or,
WITH tab AS ( SELECT <column list> FROM <table name> UNION ALL SELECT <column list> FROM <table name> ) SELECT * FROM tab;
This will help us to concentrate on your main problem rather preparing the environment first.
Got me?
Regards.
Satyaki De. 
Below is the script for the data which is in the Prod table:
with prod as ( select 1 as wk_num, 'A' as prod, 0 as qty from dual union all select 2 as wk_num, 'A' as prod, 10 as qty from dual union all select 3 as wk_num, 'A' as prod, 20 as qty from dual union all select 4 as wk_num, 'A' as prod, 15 as qty from dual union all select 5 as wk_num, 'A' as prod, 20 as qty from dual union all select 6 as wk_num, 'A' as prod, 10 as qty from dual union all select 7 as wk_num, 'A' as prod, 5 as qty from dual union all select 8 as wk_num, 'A' as prod, 50 as qty from dual union all select 9 as wk_num, 'A' as prod, 15 as qty from dual union all select 10 as wk_num, 'A' as prod, 0 as qty from dual union all select 11 as wk_num, 'A' as prod, 10 as qty from dual union all select 12 as wk_num, 'A' as prod, 30 as qty from dual union all select 13 as wk_num, 'A' as prod, 40 as qty from dual union all select 14 as wk_num, 'A' as prod, 100 as qty from dual union all select 15 as wk_num, 'A' as prod, 20 as qty from dual union all select 16 as wk_num, 'A' as prod, 40 as qty from dual union all select 17 as wk_num, 'A' as prod, 35 as qty from dual union all select 18 as wk_num, 'A' as prod, 20 as qty from dual union all select 19 as wk_num, 'A' as prod, 10 as qty from dual ) select * from prod;
hope the above helps.
Thanks, 
I think I'm missing one requirement here, on what do you want your running total exactly?
SQL> with prod as ( 2 select 1 as wk_num, 'A' as prod, 0 as qty from dual union all 3 select 2 as wk_num, 'A' as prod, 10 as qty from dual union all 4 select 3 as wk_num, 'A' as prod, 20 as qty from dual union all 5 select 4 as wk_num, 'A' as prod, 15 as qty from dual union all 6 select 5 as wk_num, 'A' as prod, 20 as qty from dual union all 7 select 6 as wk_num, 'A' as prod, 10 as qty from dual union all 8 select 7 as wk_num, 'A' as prod, 5 as qty from dual union all 9 select 8 as wk_num, 'A' as prod, 50 as qty from dual union all 10 select 9 as wk_num, 'A' as prod, 15 as qty from dual union all 11 select 10 as wk_num, 'A' as prod, 0 as qty from dual union all 12 select 11 as wk_num, 'A' as prod, 10 as qty from dual union all 13 select 12 as wk_num, 'A' as prod, 30 as qty from dual union all 14 select 13 as wk_num, 'A' as prod, 40 as qty from dual union all 15 select 14 as wk_num, 'A' as prod, 100 as qty from dual union all 16 select 15 as wk_num, 'A' as prod, 20 as qty from dual union all 17 select 16 as wk_num, 'A' as prod, 40 as qty from dual union all 18 select 17 as wk_num, 'A' as prod, 35 as qty from dual union all 19 select 18 as wk_num, 'A' as prod, 20 as qty from dual union all 20 select 19 as wk_num, 'A' as prod, 10 as qty from dual 21 ) 22 select wk_num 23 , prod 24 , qty 25 , sum(qty) over (order by wk_num) running_total 26 , case when sign(sum(qty) over (order by wk_num)) = 1 then 0 27 else sum(qty) over (order by wk_num) 28 end result 29 from prod 30 order by wk_num; WK_NUM P QTY RUNNING_TOTAL RESULT      1 A 0 0 0 2 A 10 10 10 3 A 20 30 30 4 A 15 15 15 5 A 20 5 0 6 A 10 5 5 7 A 5 10 10 8 A 50 60 60 9 A 15 75 75 10 A 0 75 75 11 A 10 65 65 12 A 30 35 35 13 A 40 5 0 14 A 100 95 95 15 A 20 75 75 16 A 40 115 115 17 A 35 150 150 18 A 20 170 170 19 A 10 180 180 19 rows selected.

Hi Mak2,
since your requirement is based on procedural rules, you could use the model clause like this:SQL> WITH prod AS ( 2 SELECT 1 wk_num,'A' prod, 0 qty FROM dual UNION ALL 3 SELECT 2 ,'A', 10 FROM dual UNION ALL 4 SELECT 3 ,'A', 20 FROM dual UNION ALL 5 SELECT 4 ,'A', 15 FROM dual UNION ALL 6 SELECT 5 ,'A', 20 FROM dual UNION ALL 7 SELECT 6 ,'A', 10 FROM dual UNION ALL 8 SELECT 7 ,'A', 5 FROM dual UNION ALL 9 SELECT 8 ,'A', 50 FROM dual UNION ALL 10 SELECT 9 ,'A', 15 FROM dual UNION ALL 11 SELECT 10 ,'A', 0 FROM dual UNION ALL 12 SELECT 11 ,'A', 10 FROM dual UNION ALL 13 SELECT 12 ,'A', 30 FROM dual UNION ALL 14 SELECT 13 ,'A', 40 FROM dual UNION ALL 15 SELECT 14 ,'A', 100 FROM dual UNION ALL 16 SELECT 15 ,'A', 20 FROM dual UNION ALL 17 SELECT 16 ,'A', 40 FROM dual UNION ALL 18 SELECT 17 ,'A', 35 FROM dual UNION ALL 19 SELECT 18 ,'A', 20 FROM dual UNION ALL 20 SELECT 19 ,'A', 10 FROM dual 21 ) SELECT wk_num, prod, qty, running_total, results FROM prod p 22 MODEL 23 PARTITION BY (prod) 24 DIMENSION BY (wk_num) 25 MEASURES (qty, 0 AS running_total, 0 AS results) 26 RULES ( 27 running_total[ANY]=CASE 28 WHEN nvl(running_total[cv()1], 0) >= 0 THEN qty[cv()] 29 ELSE nvl(running_total[cv()1], 0) + qty[cv()] 30 END, 31 results[ANY]=CASE 32 WHEN running_total[cv()] >= 0 THEN running_total[cv()] 33 ELSE 0 34 END 35 ); WK_NUM P QTY RUNNING_TOTAL RESULTS      1 A 0 0 0 2 A 10 10 10 3 A 20 20 20 4 A 15 15 0 5 A 20 35 0 6 A 10 25 0 7 A 5 20 0 8 A 50 30 30 9 A 15 15 15 10 A 0 0 0 11 A 10 10 0 WK_NUM P QTY RUNNING_TOTAL RESULTS      12 A 30 40 0 13 A 40 80 0 14 A 100 20 20 15 A 20 20 0 16 A 40 20 20 17 A 35 35 35 18 A 20 20 20 19 A 10 10 10 19 rows selected.
Regards,

Vincent 
Perhaps you are looking for this >
satyaki> satyaki>select * from v$version; BANNER  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0  Prod PL/SQL Release 10.2.0.1.0  Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0  Production NLSRTL Version 10.2.0.1.0  Production Elapsed: 00:00:00.00 satyaki> satyaki> satyaki>with prod as ( 2 select 1 as wk_num, 'A' as prod, 0 as qty from dual union all 3 select 2 as wk_num, 'A' as prod, 10 as qty from dual union all 4 select 3 as wk_num, 'A' as prod, 20 as qty from dual union all 5 select 4 as wk_num, 'A' as prod, 15 as qty from dual union all 6 select 5 as wk_num, 'A' as prod, 20 as qty from dual union all 7 select 6 as wk_num, 'A' as prod, 10 as qty from dual union all 8 select 7 as wk_num, 'A' as prod, 5 as qty from dual union all 9 select 8 as wk_num, 'A' as prod, 50 as qty from dual union all 10 select 9 as wk_num, 'A' as prod, 15 as qty from dual union all 11 select 10 as wk_num, 'A' as prod, 0 as qty from dual union all 12 select 11 as wk_num, 'A' as prod, 10 as qty from dual union all 13 select 12 as wk_num, 'A' as prod, 30 as qty from dual union all 14 select 13 as wk_num, 'A' as prod, 40 as qty from dual union all 15 select 14 as wk_num, 'A' as prod, 100 as qty from dual union all 16 select 15 as wk_num, 'A' as prod, 20 as qty from dual union all 17 select 16 as wk_num, 'A' as prod, 40 as qty from dual union all 18 select 17 as wk_num, 'A' as prod, 35 as qty from dual union all 19 select 18 as wk_num, 'A' as prod, 20 as qty from dual union all 20 select 19 as wk_num, 'A' as prod, 10 as qty from dual 21 ) 22 select k.wk_num, 23 k.prod, 24 k.qty, 25 k.running_sal, 26 case 27 when running_sal < 0 28 or qty < 0 then 29 0 30 else 31 qty 32 end result 33 from ( 34 select wk_num, 35 prod, 36 qty, 37 sum(qty) over(order by wk_num) running_sal 38 from prod 39 ) k; WK_NUM P QTY RUNNING_SAL RESULT      1 A 0 0 0 2 A 10 10 10 3 A 20 30 20 4 A 15 15 0 5 A 20 5 0 6 A 10 5 10 7 A 5 10 5 8 A 50 60 50 9 A 15 75 15 10 A 0 75 0 11 A 10 65 0 WK_NUM P QTY RUNNING_SAL RESULT      12 A 30 35 0 13 A 40 5 0 14 A 100 95 100 15 A 20 75 0 16 A 40 115 40 17 A 35 150 35 18 A 20 170 20 19 A 10 180 10 19 rows selected. Elapsed: 00:00:00.01 satyaki>
Regards.
Satyaki De. 
My model clause article (in Japanese)
http://www.geocities.jp/oraclesqlpuzzle/oow2009olapmodel.html
I used Least , greatest and model clause B)
In model clause,
If runningSum is over than 0,
I will regard runningSum was 0 B)
And Then I use greatest
when runningSum is negative,I will change runningSum is 0 ;)create table modelRun(WK_NUM,Qty) as select 1, 0 from dual union select 2, 10 from dual union select 3, 20 from dual union select 4,15 from dual union select 5,20 from dual union select 6, 10 from dual union select 7, 5 from dual union select 8, 50 from dual union select 9, 15 from dual union select 10, 0 from dual union select 11,10 from dual union select 12,30 from dual union select 13,40 from dual union select 14,100 from dual union select 15,20 from dual union select 16, 40 from dual union select 17, 35 from dual union select 18, 20 from dual union select 19, 10 from dual; select WK_NUM,Qty,greatest(runSum,0) as Result from modelRun model dimension by(WK_NUM) measures(Qty,0 as runSum) rules( runSum[any] order by WK_NUM = Least(presentv(runSum[cv()1],runSum[cv()1],0),0) +Qty[cv()]); WK_NUM QTY RESULT    1 0 0 2 10 10 3 20 20 4 15 0 5 20 0 6 10 0 7 5 0 8 50 30 9 15 15 10 0 0 11 10 0 12 30 0 13 40 0 14 100 20 15 20 0 16 40 20 17 35 35 18 20 20 19 10 10
This discussion has been closed.