|
Steven: On Mon, 17 December 2001, steven.ryan@denso.com.au wrote: > Any reason you need to use SQL? This sort of comparison can easily be done > in Query by using an option 3 join (Unmatched records with primary file). A couple possible reasons come easily to mind... Query/400 might not be installed. And "Embedded Query/400" isn't directly supported in most HLLs <g>. I'm sure others can add to the list. Even so, with a possible minor correction of a typo in the original SELECT, your Query/400 example should give the same result as the SQL example. The original SQL had A.DIVNUM = B.DIVNUM but the text of the post indicated it should be A.vendiv = B.DIVNUM That might've been an error only in the posted note, but if so, the correct SELECT would show only one side of the solution, I think -- it would only show rows in FILE1 that did not match any rows in FILE2. If I understand the problem, the complete solution might use UNION ALL to combine that SELECT with a second SELECT maybe like: SELECT * FROM FILE1 A EXCEPTION JOIN FILE2 B ON A.VENNUM = B.VENNUM AND A.vendiv = B.DIVNUM UNION ALL SELECT * FROM FILE2 C EXCEPTION JOIN FILE1 D ON C.VENNUM = D.VENNUM AND C.DIVNUM = D.vendiv and I'm not sure how this would easily be done with Query/400. Tom Liotta > "Wills, Mike N. > (TC)" > Sent by: > Subject: SQL Question (Comparing 2 files) > > I am comparing 2 files that should have similar records in each file. For > example file 1 has vennum and vendiv and file 2 has vennum and divnum. Both > files are directly related to each other and should have one unique > matching > record in each file. I am trying to figure out which records don't have a > match in the other file. How is this done? > > Is this what I should use? > SELECT * > FROM FILE1 A EXCEPTION JOIN FILE2 B > ON A.VENNUM = B.VENNUM AND > A.DIVNUM = B.DIVNUM -- Tom Liotta The PowerTech Group, Inc. 19426 68th Avenue South Kent, WA 98032 Phone 253-872-7788 Fax 253-872-7904 http://www.400Security.com ___________________________________________________ The ALL NEW CS2000 from CompuServe Better! Faster! More Powerful! 250 FREE hours! Sign-on Now! http://www.compuserve.com/trycsrv/cs2000/webmail/
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.