Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
gauravgupta2808
Warnings : 1

New User

Joined: 31 May 2007
Posts: 31
Location: Chennai, India

Posted: Wed Oct 17, 2007 2:59 am    Post subject: Selecting count and value from a table

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.

stodolas

Active Member

Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

Posted: Wed Oct 17, 2007 8:56 am    Post subject:

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
stodolas

Active Member

Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

 Posted: Wed Oct 17, 2007 9:08 am    Post subject: My query is wrong, I misunderstood the result set. I'll refine and get back to you
stodolas

Active Member

Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

Posted: Wed Oct 17, 2007 9:15 am    Post subject:

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
gauravgupta2808
Warnings : 1

New User

Joined: 31 May 2007
Posts: 31
Location: Chennai, India

 Posted: Wed Oct 17, 2007 2:03 pm    Post subject: Reply to: selecting count and value from a table 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;
stodolas

Active Member

Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

 Posted: Wed Oct 17, 2007 5:54 pm    Post subject: 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.
gauravgupta2808
Warnings : 1

New User

Joined: 31 May 2007
Posts: 31
Location: Chennai, India

 Posted: Wed Oct 17, 2007 6:08 pm    Post subject: Hi Steve, SOT & ACCOUNT and even CIN are integers.
gauravgupta2808
Warnings : 1

New User

Joined: 31 May 2007
Posts: 31
Location: Chennai, India

Posted: Mon Oct 29, 2007 10:29 pm    Post subject: Reply to: selecting count and value from a table

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]
gauravgupta2808
Warnings : 1

New User

Joined: 31 May 2007
Posts: 31
Location: Chennai, India

 Posted: Tue Oct 30, 2007 7:41 pm    Post subject: Reply to: selecting count and value from a table Could someone suggest me ?
stodolas

Active Member

Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

 Posted: Tue Oct 30, 2007 7:57 pm    Post subject: Run an explain statement and see what DB2 tells you.
gauravgupta2808
Warnings : 1

New User

Joined: 31 May 2007
Posts: 31
Location: Chennai, India

 Posted: Tue Oct 30, 2007 8:46 pm    Post subject: Reply to: Selecting count and value from a table 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
stodolas

Active Member

Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

 Posted: Tue Oct 30, 2007 8:47 pm    Post subject: Your DBA will need to create a plan table that you can use. I think there is another one also.
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Creation of Datasets dynamically on t... Raghu M N DFSORT/ICETOOL 8 Fri Mar 16, 2018 12:56 am COBOL declaration for table Poha Eater DB2 2 Mon Feb 26, 2018 6:58 pm load and delete table through JCL wit... ram_vizag JCL & VSAM 9 Thu Feb 01, 2018 12:47 am Unloading DB2 table in CSV format alo... grvtomar COBOL Programming 7 Sun Jan 28, 2018 2:05 am Skip records depends on count lakshmiibmmainframes DFSORT/ICETOOL 5 Sun Dec 24, 2017 9:51 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us