|
Jake, The left outer join will include all records from table 1 and only those records in table 2 that match records in table 1. If you have records in table 2 without matching records in table 1 they will be omitted. >select table2.csnumb2, table2.cat2, table2.amt2, table1.amt1 from > table2 left join table 1 where table1.csnumb1 = table2.csnumb2 order > by table2.csnumb2 You need to add the cat field to your join. The statement above will join a single record in table 1 to each record in table 2 with the same csnumb value. You should have something like this: select table2.csnumb2, table2.cat2, table2.amt2, table1.amt1 from table1 left join table2 on table1.csnumb1 = table2.csnumb2 and table1.cat1 = table2.cat2 order by table2.csnumb2 This will still leave out any unmatched records in table 2. To also include unmatched records in table 2 try something like this: select table2.csnumb2, table2.cat2, table2.amt2, table1.amt1 from table1 left join table2 on table1.csnumb1 = table2.csnumb2 and table1.cat1 = table2.cat2 Union select table2.csnumb2, table2.cat2, table2.amt2, table1.amt1 from table2 exception join table1 on table1.csnumb1 = table2.csnumb2 and table1.cat1 = table2.cat2 order by 1 This should combine a result containing all of table one records and matching table two records with a result of unmatched table 2 records. HTH, Rick > -----Original Message----- > From: rpg400-l-bounces@xxxxxxxxxxxx > [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jake M > Sent: Friday, April 14, 2006 2:14 PM > To: RPG programming on the AS400 / iSeries > Subject: Re: embedded SQL.... > > I think I have a problem with my select statement. If my > table2 has more number of records than my table1 then those > records are being left out....am I right? Doing a left outer > join like table1.csnumb1 = table2.csnumb2 would leave out the > records in table2...please correct me if I am wrong.. > > Jake. > > On 4/14/06, Michael_Schutte@xxxxxxxxxxxx > <Michael_Schutte@xxxxxxxxxxxx> > wrote: > > > > Don't use the prepare statement... > > > > > > C EVAL mystmt ='Select csnumb1, > cat1, amt1, > > amt2' > > C +'from > mylib.table1 left > > outer' > > C +'join > mylib.table2 on' > > C +' > > table1.csnumb1=table2.csnumb2' > > C +'and > > table1.cat1=table2.cat2' > > > > > > > > > > C/EXEC SQL > > C+ declare c1 cursor for > > C+ Select csnumb1, cat1, Sum(amt1), Sum(Amt2) from > mylib.table1 left > > C+ outer join mylib.table2 on > > C+ table1.csnumb1=table2.csnumb2 and table1.cat1=table2.cat2 > > C/END-EXEC > > > > C/EXEC SQL > > C+ OPEN C1 > > C/END-EXEC > > > > > > > > > > Dow SQLCOD >= 0 And SQLCOD <> 100 > > C/EXEC SQL > > C+ FETCH C1 INTO :Number, :Category, :Amount1, :Amount2 > > C/END-EXEC > > if SQLCOD < 0 Or SQLCOD = 100 > > leave > > endif > > print results > > enddo > > *Inlr = *on > > return > > > > > > > > > > > > Michael Schutte > > Work 614-492-7419 > > email michael_schutte@xxxxxxxxxxxx > > > > > > > > "Jake M" > > <jakeroc@xxxxxxxx > > m> > To > > Sent by: "RPG programming on > the AS400 / > > rpg400-l-bounces@ iSeries" > <rpg400-l@xxxxxxxxxxxx> > > midrange.com > cc > > > > > Subject > > 04/14/2006 02:53 embedded SQL.... > > PM > > > > > > Please respond to > > RPG programming > > on the AS400 / > > iSeries > > <rpg400-l@midrang > > e.com> > > > > > > > > > > > > > > Pro's, > > I have two tables as shown below... > > TABLE : 1 > > CSNUMB1 CAT1 AMT1 > > > > CHE1000 SI 100.00 > > CHE1100 MR 200.00 > > CHE1100 MS 300.00 > > CHE1100 OR 100.00 > > CHE1100 SI 400.00 > > CHE1200 MS 500.00 > > CHE1200 SI 100.00 > > CHE1200 OR 600.00 > > CHE1200 MR 200.00 > > > > TABLE : 2 > > CSNUMB2 CAT2 AMT2 > > > > CHE1000 MR 600.00 > > CHE1000 MS 400.00 > > CHE1000 OR 100.00 > > CHE1000 SI 500.00 > > CHE1100 SI 600.00 > > CHE1100 OR 200.00 > > CHE1100 MS 800.00 > > CHE1100 MR 900.00 > > CNA0038 OR 100.00 > > CNA0038 MS 400.00 > > CNA0038 MR 800.00 > > CNA0161 OR 700.00 > > > > I am trying to build a query which would give me something like the > > following... > > > > CSNUM CAT AMT2 AMT1 > > CHE1000 MR 600.00 0.00 > > CHE1000 MS 400.00 0.00 > > CHE1000 OR 100.00 0.00 > > CHE1000 SI 500.00 600.00 > > CHE1200 MR 0.00 200.00 > > CHE1200 MS 0.00 500.00 > > CHE1200 OR 0.00 600.00 > > CHE1200 SI 0.00 100.00 > > > > I tried high and low but in vain. I was wondering if there was any > > easy way that I am missing. I am all open for a combination > of queries > > too....Is chain a better way to go for this problem? > > > > Here is the code.... > > > > D MYSTMT S 5000A varying > > > > > > C EVAL mystmt ='Select csnumb1, > cat1, amt1, > > amt2' > > C +'from > mylib.table1 left > > outer' > > C +'join > mylib.table2 on' > > C +' > > table1.csnumb1=table2.csnumb2' > > C +'and > > table1.cat1=table2.cat2' > > > > > > > > > > C/EXEC SQL > > C+ Prepare state from :mystmt > > C/END-EXEC > > > > > > > > C/EXEC SQL > > C+ DECLARE cursor SCROLL CURSOR FOR state > > C/END-EXEC > > > > > > > > C/EXEC SQL > > C+ OPEN cursor > > C/END-EXEC > > > > > > > > > > C/EXEC SQL > > C+ FETCH..... > > C/END-EXEC > > > > C EVAL *INLR = *ON > > C RETURN > > > > > > Any help would be much appreciated. > > > > Thanks in advance. > > > > Jake. > > -- > > This is the RPG programming on the AS400 / iSeries > (RPG400-L) mailing > > list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, > > unsubscribe, or change list options, > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l > > or email: RPG400-L-request@xxxxxxxxxxxx Before posting, > please take a > > moment to review the archives at > http://archive.midrange.com/rpg400-l. > > > > > > > > -- > > This is the RPG programming on the AS400 / iSeries > (RPG400-L) mailing > > list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, > > unsubscribe, or change list options, > > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l > > or email: RPG400-L-request@xxxxxxxxxxxx Before posting, > please take a > > moment to review the archives at > http://archive.midrange.com/rpg400-l. > > > > > -- > This is the RPG programming on the AS400 / iSeries (RPG400-L) > mailing list To post a message email: RPG400-L@xxxxxxxxxxxx > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l > or email: RPG400-L-request@xxxxxxxxxxxx > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > > > Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.
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.
midrange.com
