Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 ISMF Difference between volume count ... upendrasri IBM Tools 2 Tue Dec 05, 2017 12:40 pm
No new posts Selecting a row from multiple tables! Vignesh Sid DB2 2 Thu Oct 26, 2017 6:09 pm
No new posts How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us