| Author |
Topic |
|
TJ
Posting Yak Master
201 Posts |
 Posted - 2003-04-14 : 17:38:33
|
I have the following sproc:SELECT PRODMAST_DOCNUM, PRODMAST_CUST, PRODMAST_AMT, PRODMAST_NAME, STMNT_DOCNUM, STMNT_CUST, STMNT_AMT FROM (SELECT INVnumber AS STMNT_DOCNUM, CUSTNUMBER AS STMNT_CUST,SUM(AMOUNT) AS STMNT_AMT FROM STATEMENTS GROUP BY INVnumber, CUSTNUMBER) AS A FULL OUTER JOIN (SELECT INVNUMBER AS PRODMAST_DOCNUM, CUSTNUMBER AS PRODMAST_CUST, BILL_NAME AS PRODMAST_NAME, SUM(SALEPRICE * QUANTITY) AS PRODMAST_AMT FROM PRODMAST GROUP BY INVNUMBER, CUSTNUMBER,BILL_NAME) AS Z ON Z.PRODMAST_DOCNUM = A.STMNT_DOCNUM It worked without issues UNTIL I added the column Prodmast_Name. Now I'm getting duplicated records!Is there a rule I'm unaware of? Do I have to have the same number of columns selected from both tables? I only have the Name in one table. The docnum is the linking col in the tables. Everything else is different.Thanks for reading. I hope you have an answer for me!Best regards,Teresa |
|
|
claire
Starting Member
19 Posts |
Posted - 2003-04-14 : 19:17:57
|
| Instead of "Full outer join",try "right outer join" or "right join"If you want the name appear distinctly |
 |
|
|
darinh
Yak Posting Veteran
58 Posts |
Posted - 2003-04-14 : 19:21:35
|
| Looks like it should work to me. Have you tried running your 2 subqueries seperately for a specific docnum that gives problems? This would isolate if the duplicates were appearing before your join. |
|
|
|
gwhiz
Yak Posting Veteran
78 Posts |
Posted - 2003-04-14 : 19:23:58
|
| This looks good. I set up a little test and I did not get any duplicate records unless I setup multiple Bill_Name for the same invnumber. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-15 : 09:50:58
|
My first guess is that you can have more than 1 name for each grouping. Can you produce an example of the "duplicate"?quote:
GROUP BY INVNUMBER, CUSTNUMBER,BILL_NAME) AS Z ON Z.PRODMAST_DOCNUM = A.STMNT_DOCNUM
Because of your grouping, you now have (potententially) a 1 to many relationship, but you're only joing on 2 pairs in the grouping, which s/b fine. This is how ER works. But we need to know what you mean by dups.Brett8-)Edited by - x002548 on 04/15/2003 09:52:39 |
|
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2003-04-15 : 10:00:15
|
quote:
Looks like it should work to me. Have you tried running your 2 subqueries seperately for a specific docnum that gives problems? This would isolate if the duplicates were appearing before your join.
Thank you for reminding me! Sometimes the simplest of solutions concludes the answer.There is NOTHING wrong with the sproc. It's the data! I've ran them separately and found the error was in how the data is being written to the tables. Thanks to everyone who posted. Your input gave me the clues I needed to find the solution.Best regards,Teresa |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-15 : 10:40:02
|
Uh?quote:
There is NOTHING wrong with the sproc. It's the data!
The sproc is right but the data is wrong?????????????????????The Sprocs are just an end to the means...I didn't think the data could ever be wrong...sounds like bigger fish to fry.Brett8-) |
|
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2003-04-15 : 11:01:37
|
quote:
Uh?
This is sproc is used by a Crystal Report which looks for disparities between the tables. I didn't think there were any and that issue was the sproc. It turns out that it was not. Since I pounded this issue to its death, the table, which was just put into production, was found to have incomplete and incorrect data. TJEdited by - TJ on 04/15/2003 11:02:42 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-15 : 11:28:15
|
quote:
The Sprocs are just an end to the means...I didn't think the data could ever be wrong...sounds like bigger fish to fry.
Believe me, Brett, the data can be wrong sometimes !!! All the constraints and relationships in the world can't stop someone from entering false or incorrect data in the database. - Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-15 : 11:47:36
|
Oh, absolutley....I have seen my share of gargabe data...but going live with:quote:
the table, which was just put into production, was found to have incomplete and incorrect data.
????TJ: Do you have a sample of how the data was wrong?Brett8-) |
|
|
|
|
What am I missing?