## Forum Stats

• 3,734,027 Users
• 2,246,861 Discussions

Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# Generating column based on sparsed values

Member Posts: 103
edited November 2010
Hi,

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	10```
I 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 is```
Hope I have been clear in providing the correct details.

Any hints/solutions on how to generate the above Result column would be appreciated.

• Member Posts: 1,083
```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 ?```
• Member Posts: 103
Hi,

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,
• Member Posts: 7,081
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.
• Member Posts: 103
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,
• Member Posts: 16,076 Gold Crown
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.```
• Member Posts: 35
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
• Member Posts: 7,081
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.
• Member Posts: 1,072 Bronze Badge
edited November 2010
My model clause article (in Japanese)
http://www.geocities.jp/oraclesqlpuzzle/oow2009-olap-model.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.