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
 
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: 6970
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: 6970
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: 6970
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: 6970
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 Comparing two sequential files Mohan Kothakota COBOL Programming 14 Mon Apr 16, 2018 6:35 pm
No new posts VSAM - Accessing beyond record size Benchwarmer JCL & VSAM 6 Wed Apr 11, 2018 9:20 pm
No new posts Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am
No new posts Copying only first and last word from... chandan.inst SYNCSORT 4 Wed Jan 31, 2018 4:19 pm
No new posts How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 6 Tue Jan 16, 2018 10:49 pm

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