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!
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!
Comment