× 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.



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 thread ...

Follow-Ups:
Replies:

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.