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.
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
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>
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
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
)
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.
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.
.jpg)
