|
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.table2on' > > > > > 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
