Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

FETCH key numbers which makes the error amount

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Bhargav_1058

New User


Joined: 13 Dec 2008
Posts: 53
Location: New York

PostPosted: Mon Jan 06, 2014 2:38 pm    Post subject: FETCH key numbers which makes the error amount
Reply with quote

Could anyone help me with the following SQL query?

We have a huge table with key number, account, Currency, location, error amount and date.
From the input I'll be getting Account, Currency, location, date and error amount. Now I need to find the impacted key number.

Code:
SELECT * FROM <table A>
 WHERE A.ACCOOUNT   = '<input>'
   AND A.CURRENCY   = '<input>'
   AND A.LOCATION   = '<input>'
   AND A.DATE       = '<input>'
   AND (<Input error amount> = (SELECT SUM(B.error amount)
                        FROM <table B>
                       WHERE B.ACCOUNT    = A.ACCOUNT
                         AND B.CURRENCY   = A.CURRENCY
                         AND B.LOCATION   = A.LOCATION
                         AND B.DATE       = A.DATE)) WITH UR;;;;;;


With this query I'm getting SQL +100 as return. Since the error amount could be sum of any 2 or 3 rows out of all the rows which meets the criteria specified in WHERE clause.

Example:
I need to find the impacted key numbers for the error amount 10.

Rows in the table will be as below
Code:
Account1 Currency1 Location1 Date1 Keynumber1 7 (error amount)
Account1 Currency1 Location1 Date1 Keynumber2 12
Account1 Currency1 Location1 Date1 Keynumber3 2
Account1 Currency1 Location1 Date1 Keynumber4 5
Account1 Currency1 Location1 Date1 Keynumber5 1
Account1 Currency1 Location1 Date1 Keynumber6 15


Impacted rows for the error amount could be sum of error amounts of keynumber1, 3 and 5.
Code:
Account1 Currency1 Location1 Date1 Keynumber1 7
Account1 Currency1 Location1 Date1 Keynumber3 2
Account1 Currency1 Location1 Date1 Keynumber5 1


Account, Currency, Location, Date along with Keynumber makes unique key for the table.

Is it possible to get this result using DB2 SQLs'?
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jan 06, 2014 6:31 pm    Post subject:
Reply with quote

This?
Code:
SELECT A.ACCOUNT,  A.CURRENCY, A.LOCATION, A.DATE
         , SUM(A.error_amount)
  FROM <table A>
  group by A.ACCOUNT,  A.CURRENCY, A.LOCATION, A.DATE
  having SUM(A.error_amount) >= :my-input-amount
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jan 06, 2014 7:37 pm    Post subject:
Reply with quote

Hello,

What about when the input is:

Code:
Account1 Currency1 Location1 Date1 Keynumber1  7 (error amount)
Account1 Currency1 Location1 Date1 Keynumber1A 2
Account1 Currency1 Location1 Date1 Keynumber2  12
Account1 Currency1 Location1 Date1 Keynumber3  1
Account1 Currency1 Location1 Date1 Keynumber3A 2
Account1 Currency1 Location1 Date1 Keynumber4  5
Account1 Currency1 Location1 Date1 Keynumber5  1
Account1 Currency1 Location1 Date1 Keynumber6  15


As when multiple rows fit the target amount?
Back to top
View user's profile Send private message
Bhargav_1058

New User


Joined: 13 Dec 2008
Posts: 53
Location: New York

PostPosted: Tue Jan 07, 2014 9:30 am    Post subject:
Reply with quote

I would need any combination that sums to target amount. I wouldn't bother about other rows fitting in the target amount.
Back to top
View user's profile Send private message
kameswaramanoj.Akella

New User


Joined: 28 Mar 2013
Posts: 7
Location: india

PostPosted: Tue Jan 07, 2014 10:13 am    Post subject:
Reply with quote

try something like this..

Code:
SELECT A.key number B.key number FROM <table A> <table B>       
 WHERE A.ACCOOUNT   = '<input>'
   AND A.CURRENCY   = '<input>'
   AND A.LOCATION   = '<input>'
   AND A.DATE       = '<input>'
   AND B.ACCOOUNT   = A.ACCOOUNT
   AND B.CURRENCY   = A.CURRENCY
   AND B.LOCATION   = A.LOCATION
   AND B.DATE         = A.DATE 
   AND A.key number  <> B.key number
   AND A.error amount + B.error amount = <Input error amount>
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jan 07, 2014 7:52 pm    Post subject:
Reply with quote

Hello,

Did you test this when there are more than 2 values that add up to the input error amount?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Jan 07, 2014 8:46 pm    Post subject:
Reply with quote

Sorry, I misunderstood the question.
Because you have no idea of the number of keynumbers and possible combinations, the only (decent) way to do this is recursive SQL :

Code:
WITH CTE ( ACOOUNT , CURRENCY , LOCATION , DATE , KEYNUMBER     
         , ERR_AMOUNT                                           
         , KEYLIST                                               
         , LVL ) AS (                                           
SELECT A.ACOOUNT , A.CURRENCY , A.LOCATION , A.DATE , A.KEYNUMBER
     , A.ERR_AMOUNT                                             
     , A.KEYNUMBER AS VARCHAR(200)                               
     , 1                                                         
  FROM MYTABLE A                                                 
 WHERE A.ACCOOUNT   = :INPUT-ACCOOUNT                           
   AND A.CURRENCY   = :INPUT-CURRENCY                           
   AND A.LOCATION   = :INPUT-LOCATION                           
   AND A.DATE       = :INPUT-DATE                               
   AND A.ERR_AMOUNT <= :INPUT-AMOUNT                             
UNION ALL                                                       
SELECT A.ACOOUNT , A.CURRENCY , A.LOCATION , A.DATE , A.KEYNUMBER
     , A.ERR_AMOUNT + C.ERR_AMOUNT                               
     , C.KEYLIST || ',' A.KEYNUMBER                             
     , C.LVL + 1                                                 
  FROM CTE C                                                     
     , MYTABLE A                                                 
 WHERE A.ACCOOUNT   = C.ACCOOUNT                                 
       A.CURRENCY   = C.CURRENCY                                 
       A.LOCATION   = C.LOCATION                                 
       A.DATE       = C.DATE                                     
       A.KEYNUMBER  > C.KEYNUMBER                               
   AND A.ERR_AMOUNT + C.ERR_AMOUNT <= :INPUT-AMOUNT             
   AND C.LVL < 10 )                                             
                                                                 
 SELECT * FROM CTE                                               
  WHERE  ERROR AMOUNT = :INPUT-AMOUNT


Input
Code:
AC1 CU1 LOC1 D1 KEY1 007                       
AC1 CU1 LOC1 D1 KEY2 012                       
AC1 CU1 LOC1 D1 KEY3 002                       
AC1 CU1 LOC1 D1 KEY4 005                       
AC1 CU1 LOC1 D1 KEY5 001                       
AC1 CU1 LOC1 D1 KEY6 015                       

Content of CTE after each recursion :
Code:
AC1 CU1 LOC1 D1 KEY1 007 KEY1                1
AC1 CU1 LOC1 D1 KEY3 002 KEY3                1
AC1 CU1 LOC1 D1 KEY4 005 KEY4                1
AC1 CU1 LOC1 D1 KEY5 001 KEY5                1
------------------------                       
AC1 CU1 LOC1 D1 KEY3 009 KEY1,KEY3           2
AC1 CU1 LOC1 D1 KEY4 007 KEY3,KEY4           2
AC1 CU1 LOC1 D1 KEY5 005 KEY4,KEY5           2
------------------------                       
AC1 CU1 LOC1 D1 KEY5 010 KEY1,KEY3,KEY5      3
AC1 CU1 LOC1 D1 KEY5 008 KEY3,KEY4,KEY5      3


OUTPUT:
Code:
AC1 CU1 LOC1 D1 KEY5 010 KEY1,KEY3,KEY5      3
Back to top
View user's profile Send private message
kameswaramanoj.Akella

New User


Joined: 28 Mar 2013
Posts: 7
Location: india

PostPosted: Wed Jan 08, 2014 2:40 pm    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

Did you test this when there are more than 2 values that add up to the input error amount?


Hi,

No, I gave this query only for the case where 2 values are added up to give the error amount, so that this can be extended if needed...
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 08, 2014 7:36 pm    Post subject:
Reply with quote

Hello,

Quote:
No, I gave this query only for the case where 2 values are added up to give the error amount
Why post a 2-value "solution" when the original request was not limited to 2 . . . ?

Sounds like the old saying "I couldn't fix the brakes so i made the horn louder" icon_smile.gif

I believe the "extension" of your query to a variable number of values would not be trivial - refer to the solution GuyC has posted.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Jan 08, 2014 9:04 pm    Post subject:
Reply with quote

quite some typing errors in my previous solution, this one I executed in SPUFI on a DB2 v10 database :
(First part is setting up the testdata, last part is the output of SPUFI )
Code:
WITH MYTABLE (ACOOUNT , CURRENCY , LOCATION , DAT1 , KEYNUMBER       
             , ERR_AMOUNT) AS (                                       
SELECT 'AC1','CU1','LOC1','D1','KEY1' ,  7 FROM SYSIBM.SYSDUMMY1 UNION
SELECT 'AC1','CU1','LOC1','D1','KEY1A' , 2 FROM SYSIBM.SYSDUMMY1 UNION
SELECT 'AC1','CU1','LOC1','D1','KEY2' , 12 FROM SYSIBM.SYSDUMMY1 UNION
SELECT 'AC1','CU1','LOC1','D1','KEY3' ,  2 FROM SYSIBM.SYSDUMMY1 UNION
SELECT 'AC1','CU1','LOC1','D1','KEY3A' , 2 FROM SYSIBM.SYSDUMMY1 UNION
SELECT 'AC1','CU1','LOC1','D1','KEY4' ,  5 FROM SYSIBM.SYSDUMMY1 UNION
SELECT 'AC1','CU1','LOC1','D1','KEY5' ,  1 FROM SYSIBM.SYSDUMMY1 UNION
SELECT 'AC1','CU1','LOC1','D1','KEY6' , 15 FROM SYSIBM.SYSDUMMY1 UNION
SELECT 'AC1','CU1','LOC1','D2','KEY7' ,  1 FROM SYSIBM.SYSDUMMY1)     
   , CTE (ACOOUNT , CURRENCY , LOCATION , DAT1 , KEYNUMBER           
         , ERR_AMOUNT                                                 
         , KEYLIST                                                   
         , LVL ) AS (                                                 
SELECT A.ACOOUNT , A.CURRENCY , A.LOCATION , A.DAT1 , A.KEYNUMBER     
     , A.ERR_AMOUNT                                                   
     , CAST(A.KEYNUMBER AS VARCHAR(100))                             
     , 1                                                             
  FROM MYTABLE A                                                     
 WHERE A.ACOOUNT   = 'AC1'                                           
   AND A.CURRENCY   = 'CU1'                                           
   AND A.LOCATION   = 'LOC1'                                         
   AND A.DAT1       = 'D1'                                           
   AND A.ERR_AMOUNT <= 10                                             
  UNION ALL                                                           
  SELECT A.ACOOUNT , A.CURRENCY , A.LOCATION , A.DAT1 , A.KEYNUMBER   
       , A.ERR_AMOUNT + C.ERR_AMOUNT                                 
       ,        C.KEYLIST !! ',' !! A.KEYNUMBER                       
    -- , 'X'                                                         
       , C.LVL + 1                                                   
    FROM CTE C                                                       
       , MYTABLE A                                                   
   WHERE A.ACOOUNT    = C.ACOOUNT                                     
     AND A.CURRENCY   = C.CURRENCY                                   
     AND A.LOCATION   = C.LOCATION                                   
     AND A.DAT1       = C.DAT1                                       
     AND A.KEYNUMBER  > C.KEYNUMBER                                             
     AND A.ERR_AMOUNT + C.ERR_AMOUNT <= 10                                     
     AND C.LVL < 10                                                             
   )                                                                           
                                                                               
 SELECT * FROM CTE                                                             
    WHERE  ERR_AMOUNT = 10                                                     
---------+---------+---------+---------+---------+---------+---------+---------+
ACOOUNT  CURRENCY  LOCATION  DAT1  KEYNUMBER   ERR_AMOUNT  KEYLIST             
---------+---------+---------+---------+---------+---------+---------+---------+
AC1      CU1       LOC1      D1    KEY5                10  KEY1,KEY1A,KEY5     
AC1      CU1       LOC1      D1    KEY5                10  KEY1,KEY3,KEY5       
AC1      CU1       LOC1      D1    KEY5                10  KEY1,KEY3A,KEY5     
AC1      CU1       LOC1      D1    KEY5                10  KEY1A,KEY3,KEY4,KEY5
AC1      CU1       LOC1      D1    KEY5                10  KEY1A,KEY3A,KEY4,KEY5
AC1      CU1       LOC1      D1    KEY5                10  KEY3,KEY3A,KEY4,KEY5
DSNE610I NUMBER OF ROWS DISPLAYED IS 6                                         
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100                     
Back to top
View user's profile Send private message
Bhargav_1058

New User


Joined: 13 Dec 2008
Posts: 53
Location: New York

PostPosted: Thu Jan 09, 2014 2:39 pm    Post subject:
Reply with quote

Thanks for your help Guyc! I appreciate your help!
This query seems to be little complex, also input will be unknown to select using UNION to create a dummy MYTABLE.
I have changed my approach to use CURSORS and got the input data into a PS file and used DFSORT to SUM the amounts until a match is found with error amount.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Jan 09, 2014 5:04 pm    Post subject:
Reply with quote

In the final query you wouldn't need mytable. that is only to test, because I do not have the table with your data in it.
And yes, recursion isn't simple. but neither is combinatorics.
I'm no sort specialist, but I'm sure it will do nicely.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us