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
 

 

Selecting count and value from a table

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

New User


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

PostPosted: Wed Oct 17, 2007 2:59 am    Post subject: Selecting count and value from a table
Reply with quote

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
View user's profile Send private message

stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Oct 17, 2007 8:56 am    Post subject:
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Oct 17, 2007 9:08 am    Post subject:
Reply with quote

My query is wrong, I misunderstood the result set. I'll refine and get back to you
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Oct 17, 2007 9:15 am    Post subject:
Reply with quote

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
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


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

PostPosted: Wed Oct 17, 2007 2:03 pm    Post subject: Reply to: selecting count and value from a table
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Oct 17, 2007 5:54 pm    Post subject:
Reply with quote

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
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


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

PostPosted: Wed Oct 17, 2007 6:08 pm    Post subject:
Reply with quote

Hi Steve,

SOT & ACCOUNT and even CIN are integers. icon_smile.gif
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


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

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

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
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


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

PostPosted: Tue Oct 30, 2007 7:41 pm    Post subject: Reply to: selecting count and value from a table
Reply with quote

Could someone suggest me ?
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Oct 30, 2007 7:57 pm    Post subject:
Reply with quote

Run an explain statement and see what DB2 tells you.
Back to top
View user's profile Send private message
gauravgupta2808
Warnings : 1

New User


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

PostPosted: Tue Oct 30, 2007 8:46 pm    Post subject: Reply to: Selecting count and value from a table
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Oct 30, 2007 8:47 pm    Post subject:
Reply with quote

Your DBA will need to create a plan table that you can use. I think there is another one also.
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 Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm


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