|
Gang, Just in case somebody wanted to know...this seemed easy but it is kinda tricky so here it is.... (select table1.csnumb1, table1.cat1, ifnull(table2.amt2,0.0) as amt2, ifnull(table1.amt1,0.0) as amt1 from mylib.table1 table1 left outer join mylib.table2 table2 on table1.csnumb1 = table2.csnumb2 and table1.cat1 = table2.cat2 Union select table2.csnumb2, table2.cat2,ifnull(table2.amt2,0.0) as amt2, ifnull( table1.amt1,0.0) as amt1 from mylib.table2 table2 exception join mylib.table1 table1 on table1.csnumb1 = table2.csnumb2 and table1.cat1 = table2.cat2) order by 1, 2 Thanks again, Jake. On 4/14/06, Jake M <jakeroc@xxxxxxxxx> wrote: > > Rick, > I apologize....Your query worked perfectly. That is exactly what I was > looking for...Mark Adkins suggested something along the same lines. I never > used the exception join before but now I know...Michael, thanks for your > help too..I think your option would have worked just fine but I do not have > the category in my customer master file... Thanks a bunch for all y'alls > help. I really appreciate it very much. Now back to my RPG learning > curve.....:-0).... > > Jake. > > > On 4/14/06, Jake M <jakeroc@xxxxxxxxx > wrote: > > > > Rick, > > I tried your query....I think we are really close but missing > > something...I am about to try Michael's suggestion...Rick's results..I am > > missing the other three categories from the CHE1000 customer...but I think > > we are getting close....I will be back.. > > > > > > CHE1000 SI 49318.00 15354.82 > > CHE1100 MR 148617.72 136854.88 > > CHE1100 MS 80596.88 109563.10 > > CHE1100 OR 255863.90 220504.07 > > CHE1100 SI 20370.96 80965.14 > > CHE1200 MR 23624.86 null > > CHE1200 MS 8836.08 null > > CHE1200 OR 17024.84 null > > CHE1200 SI 35559.54 null > > > > > > > > On 4/14/06, Michael_Schutte@xxxxxxxxxxxx <Michael_Schutte@xxxxxxxxxxxx > > > wrote: > > > > > > Ok Jake, it sounds like you need to join another file. You should > > > have a > > > master customer file, it'll be even better if category was in the > > > master > > > file too. > > > > > > > > > So it'll be > > > > > > Select master.customer, master.category, sum(table1.amt), sum( > > > table2.amt) > > > from master left join table1 on master.customer = table1.customer and > > > master.category = table1.category > > > left join table2 on master.customer = table2.customer and > > > master.category = table2.category > > > group by master.customer, master.category > > > > > > 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 03:56 Re: [RPG400-L] embedded SQL.... > > > PM > > > > > > > > > Please respond to > > > RPG programming > > > on the AS400 / > > > iSeries > > > <rpg400-l@midrang > > > e.com> > > > > > > > > > > > > > > > > > > > > > Michael I tried your sql statement and this is what I came up with... > > > > > > CHE1000 MR null > > > 2799.66 > > > > > > CHE1000 MS null > > > 3208.40 > > > > > > CHE1000 OR null > > > 1845.40 > > > > > > CHE1000 SI 49318.00 > > > 15354.82 > > > > > > I am posting the real amts here....they keep changing.....it works > > > great > > > but > > > for one thing....I am missing the customer numbers > > > > > CHE1200 MS 500.00 > > > > > > > > CHE1200 SI 100.00 > > > > > > > > CHE1200 OR 600.00 > > > > > > > > CHE1200 MR 200.00 > > > > > > if not for the missing customer numbers in the other table it works > > > fine....I want to include the customer numbers from both the files and > > > if > > > they have a matching category and matching customer number then I want > > > to > > > sum up the amounts under amt1 and amt2....Thanks for all the help... > > > > > > Jake. > > > > > > > > > On 4/14/06, Michael_Schutte@xxxxxxxxxxxx <Michael_Schutte@xxxxxxxxxxxx > > > > > > > wrote: > > > > > > > > Jake have you tried my suggestion.... Use STRSQL to test your query > > > > outside > > > > of RPGLE. > > > > > > > > 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 03:39 Re: [RPG400-L] embedded > > > SQL.... > > > > PM > > > > > > > > > > > > Please respond to > > > > RPG programming > > > > on the AS400 / > > > > iSeries > > > > < rpg400-l@midrang > > > > e.com> > > > > > > > > > > > > > > > > > > > > > > > > > > > > Gene, > > > > I tried the query and I get this... > > > > CSNUM CAT AMT2 AMT1 > > > > CHE1000 SI 600.00 600.00 > > > > CHE1000 SI 400.00 600.00 > > > > CHE1000 SI 100.00 600.00 > > > > CHE1000 SI 500.00 600.00 > > > > > > > > I am looking for something like this... > > > > > > > > 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 > > > > > > > > seems close but yet so far.....:(...I appreciate your input > > > though....I > > > am > > > > still fighting.. > > > > > > > > Jake. > > > > > > > > > > > > On 4/14/06, Gene Burns < burns.gene@xxxxxxxxx> wrote: > > > > > > > > > > Try something like this: > > > > > > > > > > select table2.csnumb2, table2.cat2, table2.amt2, table1.amt1 > > > > > from table2 left join table 1 > > > > > where table1.csnumb1 = table2.csnumb2 > > > > > order by table2.csnumb2 > > > > > > > > > > Gene Burns > > > > > > > > > > > > > > > Jake M wrote: > > > > > > 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 . > > > > > > > > > > > -- > > > 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 . > > > > > > > > >
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
