DB2 z/OS query that MUST be possible if yuo know what you're doing

Steven Lamb

DB2 z/OS query that MUST be possible if yuo know what you're doing

We’ve been asked to run an SQL query where the support people are interested in rows that meet two very similar criteria and they want the sum of one set of rows minus the sum of the other set of rows.

 

They want the sum of all values for each unique IDENT, DATE_POST and DATE_VALUE combination where XCRED = ‘X’ minus the sum of all values for the same IDENT, DATE_POST, DATE_VALUE combination where XCRED = ‘ ‘.

 

 

XCRED of ‘X’ represents a credit value and XCRED of ’ ‘ represents a debit value

 

I can get the individual sums for the different XCRED types and I can get a combined set of data showing both types of XCRED intermingled, but I’m stumped on trying to get a total for subtracting one figure from the other. I could extract one set of data and stick in into a temp table, but I’m sure there must be a way of doing it in a single SQL statement. Does anybody care to take up the challenge? :)

 

SELECT TAB1.IDENT,

 TAB3.DATE_POST,                              

       TAB3.DATE_VALUE,

       SUM(TAB3.A_AMOUNT) AS TOTAL    where this = Sum(XCRED = ‘X’) – Sum(XCRED = ‘ ‘)

                       

FROM TAB1  

                                       

INNER JOIN TAB2                                

   ON TAB1.CLIENT    = TAB2.CLIENT                              

  AND TAB1.IDENT    = TAB2.IDENT                        

  AND TAB2.SETT_TYPE = '002'                                    

  AND TAB2.XDELETE   = 'P' 

                                      

INNER JOIN TAB3                                  

   ON TAB2.CLIENT     = TAB3.CLIENT      

  AND TAB2.IDENT      = TAB3.IDENT

  AND TAB2.SETTM_DATE = TAB3.DATE_POST  

  AND TAB2.SETT_TYPE  = TAB3.SETTL_TYPE                           

  AND TAB3.CRUSR    = 'TB_BATCH'

  AND TAB3.XCRED      IN (' ', 'X')      

         

WHERE TAB1.CLIENT     = 'CCC'                                         

  AND TAB1.VALID_UNTIL = '99991231235959'                            

  AND TAB1.PRODINT   = 'TYPE13'                        

  AND TAB1.STATUS        = '30'                                        

 

GROUP BY TAB1.IDENT, TAB3.DATE_POST, TAB3.DATE_VALUE

ORDER BY TAB1.IDENT, TAB3.DATE_POST, TAB3.DATE_VALUE            

 

FOR READ ONLY WITH UR;  

 

Regards,

Steve

Tushar Jha

RE: DB2 z/OS query that MUST be possible if yuo know what you're doing
(in response to Steven Lamb)

Hi Steve,

Not entirely sure , if I understood your requirement correctly, but I think you can achieve this using CTEs : 

 

with XCREDX(IDENT,DATE_POST,DATE_VALUE,SUM_XCREDX_A_AMOUNT) as
(SELECT TAB1.IDENT,
TAB3.DATE_POST,
TAB3.DATE_VALUE,
SUM(TAB3.A_AMOUNT)
FROM TAB1

INNER JOIN TAB2
ON TAB1.CLIENT = TAB2.CLIENT
AND TAB1.IDENT = TAB2.IDENT
AND TAB2.SETT_TYPE = '002'
AND TAB2.XDELETE = 'P'

INNER JOIN TAB3
ON TAB2.CLIENT = TAB3.CLIENT
AND TAB2.IDENT = TAB3.IDENT
AND TAB2.SETTM_DATE = TAB3.DATE_POST
AND TAB2.SETT_TYPE = TAB3.SETTL_TYPE
AND TAB3.CRUSR = 'TB_BATCH'
AND TAB3.XCRED = 'X'

WHERE TAB1.CLIENT = 'CCC'
AND TAB1.VALID_UNTIL = '99991231235959'
AND TAB1.PRODINT = 'TYPE13'
AND TAB1.STATUS = '30'

GROUP BY TAB1.IDENT, TAB3.DATE_POST, TAB3.DATE_VALUE
) ,

XCREDB(IDENT,DATE_POST,DATE_VALUE,SUM_XCREDB_A_AMOUNT) as
(SELECT TAB1.IDENT,
TAB3.DATE_POST,
TAB3.DATE_VALUE,
SUM(TAB3.A_AMOUNT)
FROM TAB1

INNER JOIN TAB2
ON TAB1.CLIENT = TAB2.CLIENT
AND TAB1.IDENT = TAB2.IDENT
AND TAB2.SETT_TYPE = '002'
AND TAB2.XDELETE = 'P'

INNER JOIN TAB3
ON TAB2.CLIENT = TAB3.CLIENT
AND TAB2.IDENT = TAB3.IDENT
AND TAB2.SETTM_DATE = TAB3.DATE_POST
AND TAB2.SETT_TYPE = TAB3.SETTL_TYPE
AND TAB3.CRUSR = 'TB_BATCH'
AND TAB3.XCRED = ' '

WHERE TAB1.CLIENT = 'CCC'
AND TAB1.VALID_UNTIL = '99991231235959'
AND TAB1.PRODINT = 'TYPE13'
AND TAB1.STATUS = '30'

GROUP BY TAB1.IDENT, TAB3.DATE_POST, TAB3.DATE_VALUE
)

select X.IDENT,X.DATE_POST,X.DATE_VALUE,X.SUM_XCREDX_A_AMOUNT - B.SUM_XCREDB_A_AMOUNT

from XCREDX X,XCREDB B
FOR READ ONLY WITH UR;

 

Thank you.

 

Best Regards,

Tushar 

Steven Lamb

RE: DB2 z/OS query that MUST be possible if yuo know what you're doing
(in response to Tushar Jha)

Hi Tushar,

I thought my explanation wouldn't be very clear :)

If I run the SQL just using XCRED = 'X' I can get a set of rows grouped by IDENT, DATE_POST and DATE_VALUE, something like

IDENT DATE_POST   DATE VALUE  SUM(A_AMOUNT)
1     2015-10-05  2015-10-05  30.00
1     2015-10-05  2015-10-06  93.23
1     2015-11-12  2012-10-12  12.97
2     2016-01-13  2016-01-13  123.45

If I then use XCRED = ' ' (all other predicates identical) I might get

IDENT DATE_POST   DATE VALUE  SUM(A_AMOUNT)
1     2015-10-04  2015-10-05  30.00 
1     2015-10-05  2015-10-06  20.23
1     2015-11-12  2012-10-12  12.97

Only the second row in each result set matches. The final result should be the sum from XCRED = 'X' minus the corresponding sum where XCRED = ' ' for the matching rows.

IDENT DATE_POST   DATE VALUE  SUM(A_AMOUNT)
1     2015-10-05  2015-10-06  73.00

Regards,

Steve

Tushar Jha

RE: DB2 z/OS query that MUST be possible if yuo know what you're doing
(in response to Steven Lamb)

Hi Steve,

In that case, you can do a join on the two CTEs, and find out the substsraction for only matching records : 

with XCREDX(IDENT,DATE_POST,DATE_VALUE,SUM_XCREDX_A_AMOUNT) as
(SELECT TAB1.IDENT,
TAB3.DATE_POST,
TAB3.DATE_VALUE,
SUM(TAB3.A_AMOUNT)
FROM TAB1

INNER JOIN TAB2
ON TAB1.CLIENT = TAB2.CLIENT
AND TAB1.IDENT = TAB2.IDENT
AND TAB2.SETT_TYPE = '002'
AND TAB2.XDELETE = 'P'

INNER JOIN TAB3
ON TAB2.CLIENT = TAB3.CLIENT
AND TAB2.IDENT = TAB3.IDENT
AND TAB2.SETTM_DATE = TAB3.DATE_POST
AND TAB2.SETT_TYPE = TAB3.SETTL_TYPE
AND TAB3.CRUSR = 'TB_BATCH'
AND TAB3.XCRED = 'X'

WHERE TAB1.CLIENT = 'CCC'
AND TAB1.VALID_UNTIL = '99991231235959'
AND TAB1.PRODINT = 'TYPE13'
AND TAB1.STATUS = '30'

GROUP BY TAB1.IDENT, TAB3.DATE_POST, TAB3.DATE_VALUE
) ,

XCREDB(IDENT,DATE_POST,DATE_VALUE,SUM_XCREDB_A_AMOUNT) as
(SELECT TAB1.IDENT,
TAB3.DATE_POST,
TAB3.DATE_VALUE,
SUM(TAB3.A_AMOUNT)
FROM TAB1

INNER JOIN TAB2
ON TAB1.CLIENT = TAB2.CLIENT
AND TAB1.IDENT = TAB2.IDENT
AND TAB2.SETT_TYPE = '002'
AND TAB2.XDELETE = 'P'

INNER JOIN TAB3
ON TAB2.CLIENT = TAB3.CLIENT
AND TAB2.IDENT = TAB3.IDENT
AND TAB2.SETTM_DATE = TAB3.DATE_POST
AND TAB2.SETT_TYPE = TAB3.SETTL_TYPE
AND TAB3.CRUSR = 'TB_BATCH'
AND TAB3.XCRED = ' '

WHERE TAB1.CLIENT = 'CCC'
AND TAB1.VALID_UNTIL = '99991231235959'
AND TAB1.PRODINT = 'TYPE13'
AND TAB1.STATUS = '30'

GROUP BY TAB1.IDENT, TAB3.DATE_POST, TAB3.DATE_VALUE
)

select X.IDENT, X.DATE_POST, X.DATE_VALUE, X.SUM_XCREDX_A_AMOUNT - B.SUM_XCREDB_A_AMOUNT

from  XCREDX X, XCREDB B

where X.IDENT=B.IDENT 

AND  X.DATE_POST = B.DATE_POST 

AND  X.DATE_VALUE = B.X.DATE_VALUE 


FOR READ ONLY WITH UR;

 

Thank you.

 

Regards,

Tushar

Steven Lamb

RE: DB2 z/OS query that MUST be possible if yuo know what you're doing
(in response to Tushar Jha)

Thanks Tushar - I've run it for one particular IDENT value and the results look very promising!

I've sent the test results to the support team now. If they're happy I'll run it for the remaining 7.5 million :)

 

Regards,

Steve

Tushar Jha

RE: DB2 z/OS query that MUST be possible if yuo know what you're doing
(in response to Steven Lamb)

Glad to hear that, Steve.

Thank you.

 

Best Regards,

Tushar

Michael Hannan

RE: DB2 z/OS query that MUST be possible if yuo know what you're doing
(in response to Steven Lamb)

Steve,

Does not sound so difficult, for your basic requirement:

They want the sum of all values for each unique IDENT, DATE_POST and DATE_VALUE combination where XCRED = ‘X’ minus the sum of all values for the same IDENT, DATE_POST, DATE_VALUE combination where XCRED = ‘ ‘.

You can enhance this query to cover any other criteria.I tried to keep it as simple as possible.

SELECT IDENT
       , DATE_POST
       , DATE_VALUE
       ,SUM(DECODE(XCRED, 'X', 1, -1) * VALUE) AS MINUS_RESULT
FROM table
WHERE XCRED IN (' ', 'X')
GROUP BY IDENT
       , DATE_POST
       , DATE_VALUE
HAVING MAX(XCRED = 'X') AND MIN(XCRED)= ' '
WITH UR;

DECODE is a short hand method for CASE construct.
If you need it to perform better than this I can give you a more complex query.

 
In Reply to Steven Lamb:

We’ve been asked to run an SQL query where the support people are interested in rows that meet two very similar criteria and they want the sum of one set of rows minus the sum of the other set of rows.

 

They want the sum of all values for each unique IDENT, DATE_POST and DATE_VALUE combination where XCRED = ‘X’ minus the sum of all values for the same IDENT, DATE_POST, DATE_VALUE combination where XCRED = ‘ ‘.

 

 

XCRED of ‘X’ represents a credit value and XCRED of ’ ‘ represents a debit value

 

I can get the individual sums for the different XCRED types and I can get a combined set of data showing both types of XCRED intermingled, but I’m stumped on trying to get a total for subtracting one figure from the other. I could extract one set of data and stick in into a temp table, but I’m sure there must be a way of doing it in a single SQL statement. Does anybody care to take up the challenge? :)

 

SELECT TAB1.IDENT,

 TAB3.DATE_POST,                              

       TAB3.DATE_VALUE,

       SUM(TAB3.A_AMOUNT) AS TOTAL    where this = Sum(XCRED = ‘X’) – Sum(XCRED = ‘ ‘)

                       

FROM TAB1  

                                       

INNER JOIN TAB2                                

   ON TAB1.CLIENT    = TAB2.CLIENT                              

  AND TAB1.IDENT    = TAB2.IDENT                        

  AND TAB2.SETT_TYPE = '002'                                    

  AND TAB2.XDELETE   = 'P' 

                                      

INNER JOIN TAB3                                  

   ON TAB2.CLIENT     = TAB3.CLIENT      

  AND TAB2.IDENT      = TAB3.IDENT

  AND TAB2.SETTM_DATE = TAB3.DATE_POST  

  AND TAB2.SETT_TYPE  = TAB3.SETTL_TYPE                           

  AND TAB3.CRUSR    = 'TB_BATCH'

  AND TAB3.XCRED      IN (' ', 'X')      

         

WHERE TAB1.CLIENT     = 'CCC'                                         

  AND TAB1.VALID_UNTIL = '99991231235959'                            

  AND TAB1.PRODINT   = 'TYPE13'                        

  AND TAB1.STATUS        = '30'                                        

 

GROUP BY TAB1.IDENT, TAB3.DATE_POST, TAB3.DATE_VALUE

ORDER BY TAB1.IDENT, TAB3.DATE_POST, TAB3.DATE_VALUE            

 

FOR READ ONLY WITH UR;  

 

Regards,

Steve



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Steven Lamb

RE: DB2 z/OS query that MUST be possible if yuo know what you're doing
(in response to Michael Hannan)

Thanks Chaps.

 

The wonders of SQL! The "trouble" is that I'm more of a sysprog than an application DBA and I've never been a developer, so this kind of query exposes the limitations of my SQL skills.

 

Regards,

Steve

Michael Hannan

RE: DB2 z/OS query that MUST be possible if yuo know what you're doing
(in response to Steven Lamb)

My SQL maybe better than my English. My use of English each day can be less than the SQL use. Most problems have been solved already at least in some similar form.

 In Reply to Steven Lamb:

Thanks Chaps.

 

The wonders of SQL! The "trouble" is that I'm more of a sysprog than an application DBA and I've never been a developer, so this kind of query exposes the limitations of my SQL skills.

 

Regards,

Steve



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Tommy Petersen

RE: DB2 z/OS query that MUST be possible if yuo know what you're doing
(in response to Steven Lamb)

I would use the CASE expression:

SELECT TAB1.IDENT,

 TAB3.DATE_POST,                              

       TAB3.DATE_VALUE,

       SUM(case when xred = 'x' then tab3.A_AMOUNT

                         when xred = ' ' then tab3.A_AMOUNT * -1) ) AS TOTAL                           

FROM TAB1 

The rest would remain unaltered, I am not 100% sure the syntax is correct.

I also like Michael Hannan's suggestion but don't include the HAVING clause as that will exclude those sums that do not have rows with both X and space in XCRED, however unlikely that may be.

Steven Lamb

RE: DB2 z/OS query that MUST be possible if yuo know what you're doing
(in response to Tommy Petersen)

From the description they gave us for what they wanted, we initially thought that they only required the total for dates when XCRED had both X and blank rows. Of course this eventually turned out to be wrong and they did want all rows. I had actually managed to get a version of this myself so I feel slightly better now, although it was a variation on Tommy's SQL. I used

SUM(CASE WHEN XCRED = 'X' THEN TAB3.A_AMOUNT
                    WHEN XCRED = ' '  THEN 0-TAB3.A_AMOUNT    END ) AS TOTAL                           

Michael Hannan

RE: DB2 z/OS query that MUST be possible if yuo know what you're doing
(in response to Steven Lamb)

Steven,

My HAVING clause that insisted on having both 'X" entries and ' ' entries could have been omitted, if that was not a requirement.

My DECODE function actually was intended to do exactly the same as what your CASE version does. 

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Dec 14, 2017 - 05:46 AM (Europe/Berlin)