Problem with calculate difference between rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

roberto - 30 Oct 2008 17:50 GMT

I have the following problem:

In a view I obtain three rows and I have to group them together making
some particular operation to add dinamic column.

The query without the group by give me these results :

CMP    CNT    CODE    GROUP    ENDQTY    ROWPARTITIONED
001    PRD    100    AAA    20        1
001    PRD    100    BBB    50        2
001    PRD    100    CCC    100        3

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

CMP    CNT    CODE    GROUP    ENDQTY    ROWPARTITIONED
001    PRD    100    AAA    20        1
001    PRD    110    BBB    50        1
001    PRD    120    CCC    100        1

In the first case what I want is this one

CMP    CNT    CLM1    CLM2    CLM3
001    PRD    20    30    50

in the second case what I want is

CMP    CNT    CLM1    CLM2    CLM3
001    PRD    20    50    100

The columns(CLMx) must be calculated in the following way :

If the ROWPARTITIONED column is equal to 1 the the column is equal to
ENDQTY

If the ROWPARTITIONED column is greater then 1 the the column is equal
to ENDQTY -( ENDQTY of the previous group)
CLM2 = 30 because ENDQTY wit hrow partiioned = 2 --> 50 and ENDQTY of
the previous row = 20... so 50-20 = 30

What I have done is a the following query :

SELECT CMP, CNT, SUM(CASE WHEN GROUP = 'AAA' THEN ENDQTY ELSE 0 END)
CLM1,
SUM(CASE WHEN GROUP = 'BBB' THEN ENDQTY ELSE 0 END) - SUM(CASE WHEN
GROUP = 'AAA' THEN ENDQTY ELSE 0 END) CLM2,
SUM(CASE WHEN GROUP = 'CCC' THEN ENDQTY ELSE 0 END) - SUM(CASE WHEN
GROUP = 'BBB' THEN ENDQTY ELSE 0 END) CLM3

GROUP BY CMP, CNT

This query gives me the possibility to obtain a single row but I'm not
able to write something like this

CASE WHEN ROWPARTITIONED > 1 THEN SUM(CASE WHEN GROUP = 'BBB' THEN
ENDQTY ELSE 0 END) - SUM(CASE WHEN GROUP = 'AAA' THEN ENDQTY ELSE 0
END) ELSE SUM(CASE WHEN GROUP = 'BBB' THEN ENDQTY ELSE 0 END) END

because I obtain an sql error (SQLCODE -119) due to the fact that the
field ROWPARTITIONED is in select and not in group by.

I don't want to use (if it is possible) an "inner/sub" select in each
CASE statement, becuase this is only a little part of the real very
complex statement.

Can someone help me pls?

Thank You very much (in advance) to all for the support!

bye!

Reply to this Message

Lennart - 30 Oct 2008 20:26 GMT

> I have the following problem:
>
[quoted text clipped - 65 lines]
>
> bye!

I'll leave the "pivoting" for you, but I think you should be able to
use a technique like:

[lelle@53dbd181 ~]$ db2 "with T (cmp, cnt, code, grp, endqty,
rowpartitioned) as ( values ('001', 'PRD', 100, 'AAA', 20, 1), ('001',
'PRD', 100, 'BBB', 50, 2), ('001', 'PRD', 100, 'CCC', 100, 3) ) select
cmp, cnt, endqty - ( case when rowpartitioned > 1 then 1 else 0 end *
coalesce(sum(endqty) over (order by grp rows between 1 preceding and 1
preceding), 0) ) from T"

CMP CNT 3
--- --- -----------
001 PRD 20
001 PRD 30
001 PRD 50

3 record(s) selected.

[lelle@53dbd181 ~]$ db2 "with T (cmp, cnt, code, grp, endqty,
rowpartitioned) as ( values ('001', 'PRD', 100, 'AAA', 20, 1), ('001',
'PRD', 100, 'BBB', 50, 1), ('001', 'PRD', 100, 'CCC', 100, 1) ) select
cmp, cnt, endqty - ( case when rowpartitioned > 1 then 1 else 0 end *
coalesce(sum(endqty) over (order by grp rows between 1 preceding and 1
preceding), 0) ) from T"

CMP CNT 3
--- --- -----------
001 PRD 20
001 PRD 50
001 PRD 100

to accomplish your goal.

HTH
/Lennart

Reply to this Message

Tonkuma - 30 Oct 2008 22:51 GMT

You can use "MAX(ROWPARTITIONED) > 1".

But, I have some questions.
1) Do you always get 3 rows?
2) If so, are groups always 'AAA', 'BBB' and 'CCC'?

Anyway, following example may give you some hints.
------------------------------ Commands Entered
------------------------------
WITH t(cmp, cnt, code, grp, endqty) AS (
VALUES -- rn ct
('001', 'PRD', 100, 'AAA', 20) -- 1 3
,('001', 'PRD', 100, 'BBB', 50) -- 2 3
,('001', 'PRD', 100, 'CCC', 100) -- 3 3
,('002', 'PRD', 100, 'AAA', 20) -- 1 1
,('002', 'PRD', 110, 'BBB', 50) -- 2 1
,('002', 'PRD', 120, 'CCC', 100) -- 3 1
)
,add_partitioned AS (
SELECT t.*
, ROW_NUMBER()
OVER(PARTITION BY cmp, cnt
ORDER BY grp)
AS rn
, COUNT(*)
OVER(PARTITION BY cmp, cnt, code)
AS ct
FROM t
)
SELECT cmp
, cnt
, SUM(CASE rn
WHEN 1 THEN
endqty
END
) AS clm1
, SUM(CASE rn
WHEN 1 THEN
CASE
WHEN ct > 1 THEN
- endqty
END
WHEN 2 THEN
endqty
END
) AS clm2
, SUM(CASE rn
WHEN 2 THEN
CASE
WHEN ct > 1 THEN
- endqty
END
WHEN 3 THEN
endqty
END
) AS clm3
FROM add_partitioned
GROUP BY
cmp, cnt
;
------------------------------------------------------------------------------

CMP CNT CLM1 CLM2 CLM3
--- --- ----------- ----------- -----------
001 PRD 20 30 50
002 PRD 20 50 100

2 record(s) selected.

Reply to this Message

roberto - 31 Oct 2008 17:39 GMT

Thank you very much to all for the reply.
Tonkuma: no, I haven't alwyas 3 rows

but fortunatelly both Tonkuma and Lennart give me same very good
suggestion. and I solved (with your help) my problem.

Thanks again!
Roberto

Reply to this Message

Tonkuma - 31 Oct 2008 18:30 GMT

> but fortunatelly both Tonkuma and Lennart give me same very good
> suggestion. and I solved (with your help) my problem.
>
> Thanks again!
> Roberto

I feel very glad, if I could give you some help to solve your problem.

Reply to this Message