× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I read Richard's post to be examples of the things to which you would apply the 
union - an example of getting matching records and one of getting only the 
fields from the first file by using the exception join. The key thing there is 
to qualify the "all fields" asterisk with the file (alias) name.

-------------- Original message -------------- 

> A join is not the same as a union. 
> 
> For example 
> select * from jhhl1 
> union 
> select * from jhhl2 
> returns 
> ....+....1.... 
> MYKEY MYCHAR 
> A Test A 
> B Test B 
> C Test C 
> D Test D 
> E Test E 
> F Test F 
> ******** End 
> 
> While 
> select a.*, b.* from jhhl1 a 
> join jhhl2 b on a.mykey=b.mykey 
> returns nothing 
> ....+....1....+....2....+....3....+. 
> MYKEY MYCHAR MYKEY MYCHAR 
> ******** End of data ******** 
> 
> Summary: A union returns separate rows from each table while a join 
> combines rows from multiple tables into a single row. 
> 
> 
> Hey, I noticed that if I change the contents of jhhl3 from 
> ....+....1... 
> MYKEY MYCHAR 
> C Test 1 
> F Test 2 
> ******** End 
> to 
> ....+....1... 
> MYKEY MYCHAR 
> C Test C 
> F Test F 
> ******** End 
> 
> Then I can do 
> select * from jhhl1 
> union 
> select * from jhhl2 
> except 
> select * from jhhl3 
> 
> and get 
> ....+....1... 
> MYKEY MYCHAR 
> A Test A 
> B Test B 
> D Test D 
> E Test E 
> ******** End 
> 
> However, if I leave MYCHAR back at their original values then I get six 
> rows. EXCEPT and INTERSECT are in the section "What's new for V5R3 in the 
> SQL Reference Book". Looks like you'll have to use my earlier suggestion 
> for obsolete versions of the operating system. 
> 
> Rob Berendt 
> -- 
> Group Dekko Services, LLC 
> Dept 01.073 
> PO Box 2000 
> Dock 108 
> 6928N 400E 
> Kendallville, IN 46755 
> http://www.dekko.com 
> 
> 
> 
> 
> 
> "Richard Casey" 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx 
> 11/23/2005 02:03 PM 
> Please respond to 
> Midrange Systems Technical Discussion 
> 
> 
> To 
> "Midrange Systems Technical Discussion" 
> cc 
> 
> Fax to 
> 
> Subject 
> RE: Yet another SQL newbie question 
> 
> 
> 
> 
> 
> 
> James, 
> 
> If INTERSECT means to select records from one file that have matching 
> records in another file, you can use an INNER JOIN. 
> 
> SELECT A.* FROM FILE1 
> INNER JOIN FILE2 B ON (A.FIELD1=B.FIELD1 AND A.FIELD2=B.FIELD2 ...) 
> 
> That will select all the records from FILE1 that have a matching record in 
> FILE2. 
> 
> If EXCEPT means to select records from one file that do NOT have matching 
> record in another file, you can use an EXCEPTION JOIN. 
> 
> SELECT A.* FROM FILE1 
> EXCEPTION JOIN FILE2 B ON (A.FIELD1=B.FIELD1 AND A.FIELD2=B.FIELD2 ...) 
> 
> That will select all the records from FILE1 that do NOT have a matching 
> record in FILE2. 
> 
> Hope this helps! 
> Richard 
> 
> -----Original Message----- 
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of James H H Lampert 
> Sent: Wednesday, November 23, 2005 1:37 PM 
> To: midrange-l@xxxxxxxxxxxx 
> Subject: Yet another SQL newbie question 
> 
> 
> My fellow geeks: 
> 
> My SQL project is progressing nicely, but I've run into 
> yet another place where I'm at a bit of a loss. 
> 
> I need to be able to UNION or INTERSECT an arbitrary 
> number of identically-structured files, and I need to, at 
> times, EXCEPT records from that intersection or union, 
> based on the records in yet another identically-structured 
> file. 
> 
> I note that AS/400 SQL, at least at V4, has a UNION, but 
> not an INTERSECT or an EXCEPT. Can anybody suggest where I 
> should be looking for an alternate way to do this? 
> 
> -- 
> JHHL 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
> 

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.