|
View previous topic :: View next topic
|
| Author |
Message |
gauravgupta2808
Warnings : 1
New User
Joined: 31 May 2007
Posts: 31
Location: Chennai, India
|
|
|
|
I have a table containing 3 columns (CIN, Sort, Account) where combination of (SORT&Account) gives a unique account number.
A account number(SORT&Account) can be owned by 2 or more CINs (which is concept of joint accounts)
Suppose i have data as:
| Quote: |
CIN SORT ACCOUNT
A 10001 10001
A 10002 10001
A 10002 10004
B 10002 10000
B 10002 10001
C 10001 10000
C 10002 20000
D 10001 10001
|
I need to write a DB2 SELECT QUERY to get the SORT&ACCOUNT for any CIN and the count of Joint CINs for the input CIN
Ex
1. If input is A
10001 10001 2
10002 10001 1
10002 10004 1
2. If input is B
10002 10000 1
10002 10001 2
3. If input is C
10001 10000 1
10002 20000 1
4.If input is D
10001 10001 2
Could some help me on this please. |
|
| Back to top |
|
 |
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
I don't have your table, I'll try and find time to build it and test the following to confirm working status.
| Code: |
SELECT SORT, ACCOUNT, Sum(1)
FROM table
WHERE CIN = 'A'
Group By Sort, Account
|
|
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| My query is wrong, I misunderstood the result set. I'll refine and get back to you |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
I assume this is wrong.
| Quote: |
1. If input is A
10001 10001 2
10002 10001 1
10002 10004 1
|
Based on your table it should be
1. If input is A
10001 10001 2
10002 10001 2
10002 10004 1
Here is a working query:
| Code: |
SELECT SORT, Account, SUM(1) AS Expr1
FROM Table1
WHERE Sort + Account IN
(SELECT SORT + ACCOUNT
FROM Table1
WHERE Cin = 'D')
GROUP BY SORT, Account
|
|
|
| Back to top |
|
|
gauravgupta2808
Warnings : 1
New User
Joined: 31 May 2007
Posts: 31
Location: Chennai, India
|
|
|
|
Hi,
When we do (Sort + Account) it adds them in the result, which i dont feel is correct,
because:
CIN Sort Account
A 10002 10005
B 10001 10006
Both the above will be considered as Joint Account.
Well i have traced a solution from a friend Varun who works on database:
Select Count(*), Sortcode, account from table where (sortcode, account) in
(select sortcode, account from table where cust ="a")
Group by sortcode,account; |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
I assumed the Sort and Account were CHAR or VARCHAR fields. If that is the case, my query works. Is it not possible for an account to be something like A00002? I could have used the concat function also.
I had tried the solution from Varun, but it was late and I was a little off on the syntax. That is a better solution. |
|
| Back to top |
|
|
gauravgupta2808
Warnings : 1
New User
Joined: 31 May 2007
Posts: 31
Location: Chennai, India
|
|
|
|
Hi Steve,
SOT & ACCOUNT and even CIN are integers.  |
|
| Back to top |
|
|
gauravgupta2808
Warnings : 1
New User
Joined: 31 May 2007
Posts: 31
Location: Chennai, India
|
|
|
|
Hi,
I was using this query
| Code: |
Select Count(*), Sortcode, account
FROM table where (sortcode, account) IN
(select sortcode, account from table where cust ="a")
Group by sortcode,account;
|
I was told that the above query leads to performance issues, so i was suggested to use:
| Code: |
Select Count(*), A.Sortcode, A.account
FROM table A, table B
where
A.Sortcode = B.Sortcode
AND A.account = B.accont
AND B.cust ="a"
Group by sortcode,account;
|
I just want to know if both the above queries give the same result always.[/code] |
|
| Back to top |
|
|
gauravgupta2808
Warnings : 1
New User
Joined: 31 May 2007
Posts: 31
Location: Chennai, India
|
|
|
|
| Could someone suggest me ? |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Run an explain statement and see what DB2 tells you. |
|
| Back to top |
|
|
gauravgupta2808
Warnings : 1
New User
Joined: 31 May 2007
Posts: 31
Location: Chennai, India
|
|
|
|
I was not aware of the PLAN_TABLE.
I tried to execute the SQL after reading the explaination in 'qw explain', but i am getting error
SQLCODE = -219, ERROR: THE REQUIRED EXPLANATION TABLE TTOI.PLAN_TABLE DOES NOT EXIST |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Your DBA will need to create a plan table that you can use. I think there is another one also. |
|
| Back to top |
|
|
|
|
