Re: SQL Question (Comparing 2 files)

[thomas@xxxxxxxxxxx]

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/