|
View previous topic :: View next topic
|
| Author |
Message |
Bhargav_1058
New User
.jpg)
Joined: 13 Dec 2008
Posts: 53
Location: New York
|
|
|
|
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 |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Bhargav_1058
New User
Joined: 13 Dec 2008
Posts: 53
Location: New York
|
|
|
|
| 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 |
|
|
kameswaramanoj.Akella
New User
Joined: 28 Mar 2013
Posts: 7
Location: india
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Did you test this when there are more than 2 values that add up to the input error amount? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
kameswaramanoj.Akella
New User
Joined: 28 Mar 2013
Posts: 7
Location: india
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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" 
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
Bhargav_1058
New User
Joined: 13 Dec 2008
Posts: 53
Location: New York
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
