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
 

 

Extaract record from 2 tables by comparing column

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

New User


Joined: 26 Oct 2006
Posts: 50
Location: Chennai

PostPosted: Mon Oct 04, 2010 1:31 pm    Post subject: Extaract record from 2 tables by comparing column
Reply with quote

Dear Friends,

Can some one help me to resolve my problem? Below is my requirement.

Table 1 is having Sum of(Sales) from Table2 by ID level. So sum of (Sales) by Sub_id level should be same in ID level in Table1.

But there are some mismatch record which I need to extract.
I tired with subselect etc.but I can't see the correct o/p.

Below is the sample input and output

Table1:

Code:
ID   Sales   Cust_Nbr
1000   500   100001
1000   1000   100002
2000   1000   100003
3000   1000   100004


Table2:
Code:
ID   Sub_ID Sales   Cust Nbr
1000   1     300   10001
1000   2     200   10001
1000   1     500   10002
1000   3     400   10002
2000   2     400   10003
2000   3     400   10003
3000   2     500   10004
3000   3     500   10004


Output:

Sales from Table 2 is not match with Sales in Table1 for ID 1000 and Cust Nbr 10002. Same for Cust Nbr 10003

So below should be result.

Code:
ID   T1.Cust_Nbr    T1.Sales Sub_ID   T2.Sales
1000   100002          1000       1   500
                                  3   400

2000   100003          1000       2   400
                                  3   400


Appriciate if some one help on this
Code:
Code:
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Oct 04, 2010 1:39 pm    Post subject:
Reply with quote

so, you are looking for
t1.id
t1.cust_nbr
t1.sales
t2.sub_id
t2.sales
from t1, t2
where t1.id = t2.id
and t1.cust_nbr = t2.cust.nbr
and sum(t2.sales) <> t1.sales

what sql did you try? and what where the results.
Back to top
View user's profile Send private message
bbharathiraj
Warnings : 1

New User


Joined: 26 Oct 2006
Posts: 50
Location: Chennai

PostPosted: Mon Oct 04, 2010 2:10 pm    Post subject:
Reply with quote

Yes Sir. This is what I am trying. But I am getting 815 sqlcode if I using Group by Sub_id for sum of T2.sales. I am not getting result
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Oct 04, 2010 2:16 pm    Post subject:
Reply with quote

it would help if you provide your sql.
Back to top
View user's profile Send private message
bbharathiraj
Warnings : 1

New User


Joined: 26 Oct 2006
Posts: 50
Location: Chennai

PostPosted: Mon Oct 04, 2010 2:33 pm    Post subject:
Reply with quote

Select t1.id,t1.cust_nbr,t1.sales,t2.sub_id,t2.sales
from t1, t2
where t1.id = t2.id
and t1.cust_nbr = t2.cust.nbr
and t1.sales <> (select sum(t2.sales) from t2 A
where t1.cust_nbr=t2.cust_nbr and t1.id=t2.id)

But this query, also retrive the records which is having same sales value in T1 and T2. If I tune somthing else I have ended with sqlcode error
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Oct 04, 2010 2:49 pm    Post subject:
Reply with quote

Quote:

and t1.sales <> (select sum(t2.sales) from t2 A
where t1.cust_nbr=t2.cust_nbr and t1.id=t2.id)


why not (don't know if it will work, but it does not seem congruent to reference T2. in the subselect instead of A):
Code:

and t1.sales <> (select sum(A.sales)
                   from t2 A
                  where t1.cust_nbr = A.cust_nbr
                    and t1.id       = A.id)
Back to top
View user's profile Send private message
bbharathiraj
Warnings : 1

New User


Joined: 26 Oct 2006
Posts: 50
Location: Chennai

PostPosted: Mon Oct 04, 2010 3:07 pm    Post subject:
Reply with quote

Yes Sir. Its working fine. Thanks a lot icon_smile.gif I didn't noticed this

But the output showing like below (duplicate record)
Is there any way to get exactly which Ihave mentioned in my

Code:
ID   T1.Cust_Nbr    T1.Sales Sub_ID   T2.Sales
1000   100002          1000       1   500
1000   100002          1000       3  400

2000   100003          1000       2   400
2000   100003          1000       3   400


requirement?
By displaying only one record from Table1 and relevent record from Table 2.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Mon Oct 04, 2010 3:32 pm    Post subject:
Reply with quote

other than reminding you that db2 does not have records,
db2 has rows,

i would say no,
but someone else will come along and provide a solution, if it exists.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Oct 04, 2010 3:35 pm    Post subject:
Reply with quote

AFAIK not within DB2.
In a QMF report or any other reporting tool , this should be easy.
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 Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
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 Sort Large record length cmsmoon DFSORT/ICETOOL 14 Tue Apr 11, 2017 5:49 pm
No new posts Format record to remove Leading zeroes Learncoholic DFSORT/ICETOOL 14 Wed Apr 05, 2017 2:43 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


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