I have the following DB2 table:
COLUMN_NAME | DATA_TYPE | TYPE_NAME | COLUMN_SIZE | COLUMN_TEXT |
---|---|---|---|---|
DMPROD | -2 | CHAR () FOR BIT DATA | 35 | Product Code |
DMPTYP | -2 | CHAR () FOR BIT DATA | 1 | Period Type |
DMTYPE | -2 | CHAR () FOR BIT DATA | 6 | Type of Data |
DMVL01 | 3 | DECIMAL | 17 | Value Period 1 |
DMVL02 | 3 | DECIMAL | 17 | Value Period 2 |
DMVL03 | 3 | DECIMAL | 17 | Value Period 3 |
DMVL04 | 3 | DECIMAL | 17 | Value Period 4 |
DMVL05 | 3 | DECIMAL | 17 | Value Period 5 |
DMVL06 | 3 | DECIMAL | 17 | Value Period 6 |
DMVL07 | 3 | DECIMAL | 17 | Value Period 7 |
DMVL08 | 3 | DECIMAL | 17 | Value Period 8 |
DMVL09 | 3 | DECIMAL | 17 | Value Period 9 |
DMVL10 | 3 | DECIMAL | 17 | Value Period 10 |
DMVL11 | 3 | DECIMAL | 17 | Value Period 11 |
DMVL12 | 3 | DECIMAL | 17 | Value Period 12 |
DMYEAR | 3 | DECIMAL | 4 | Fiscal Year |
Below query would return the sum of value periods for each DMTYP:
SELECT D.DMPROD, Sum(CASE WHEN DMTYPE = 'SLSGSV' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS COST, Sum(CASE WHEN DMTYPE = 'RTNCST' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS RTNCST, Sum(CASE WHEN DMTYPE = 'RTNNET' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS RTNNET, Sum(CASE WHEN DMTYPE = 'RTNQTY' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS RTNQTY, Sum(CASE WHEN DMTYPE = 'RTNVAL' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS RTNVAL, Sum(CASE WHEN DMTYPE = 'SLSGSV' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS SLSGSV, Sum(CASE WHEN DMTYPE = 'SLSLST' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS SLSLST, Sum(CASE WHEN DMTYPE = 'SLSNIV' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS SLSNIV, Sum(CASE WHEN DMTYPE = 'SLSNTN' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS SLSNTN, Sum(CASE WHEN DMTYPE = 'SLSQTY' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS SLSQTY, Sum(CASE WHEN DMTYPE = 'XXD' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS XXD FROM DWM D WHERE D.DMYEAR IN (2022) AND D.DMPTYP = 'M' GROUP BY D.DMPROD ORDER BY 1;
Output is what I want, except value periods cannot easily be changed in this query without rewriting all sums. For example, say I only want to see Value Period 1-5 in DMYEAR 2022, I would need to change every case statement. Would there be an easier way, perhaps with subqueries, to keep the SUM statements the same but select only the desired value periods for the final output?
Advertisement
Answer
You could solve this using a subquery:
SELECT D.DMPROD, Sum(CASE WHEN DMTYPE = 'SLSGSV' THEN val END) AS COST, Sum(CASE WHEN DMTYPE = 'RTNCST' THEN val END) AS RTNCST, Sum(CASE WHEN DMTYPE = 'RTNNET' THEN val END) AS RTNNET, Sum(CASE WHEN DMTYPE = 'RTNQTY' THEN val END) AS RTNQTY, Sum(CASE WHEN DMTYPE = 'RTNVAL' THEN val END) AS RTNVAL, Sum(CASE WHEN DMTYPE = 'SLSGSV' THEN val END) AS SLSGSV, Sum(CASE WHEN DMTYPE = 'SLSLST' THEN val END) AS SLSLST, Sum(CASE WHEN DMTYPE = 'SLSNIV' THEN val END) AS SLSNIV, Sum(CASE WHEN DMTYPE = 'SLSNTN' THEN val END) AS SLSNTN, Sum(CASE WHEN DMTYPE = 'SLSQTY' THEN val END) AS SLSQTY, Sum(CASE WHEN DMTYPE = 'XXD' THEN val END) AS XXD FROM (SELECT D.*, (D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05) as val FROM DWM D ) D WHERE D.DMYEAR IN (2022) AND D.DMPTYP = 'M' GROUP BY D.DMPROD ORDER BY 1;