|
View previous topic :: View next topic
|
| Author |
Message |
bbharathiraj
Warnings : 1
New User
Joined: 26 Oct 2006
Posts: 50
Location: Chennai
|
|
|
|
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
|
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
bbharathiraj
Warnings : 1
New User
Joined: 26 Oct 2006
Posts: 50
Location: Chennai
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| it would help if you provide your sql. |
|
| Back to top |
|
|
bbharathiraj
Warnings : 1
New User
Joined: 26 Oct 2006
Posts: 50
Location: Chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
bbharathiraj
Warnings : 1
New User
Joined: 26 Oct 2006
Posts: 50
Location: Chennai
|
|
|
|
Yes Sir. Its working fine. Thanks a lot  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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
AFAIK not within DB2.
In a QMF report or any other reporting tool , this should be easy. |
|
| Back to top |
|
|
|
|
