Re: embedded SQL....

["Jake M" <jakeroc@xxxxxxxxx>]

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)
> C+ from mylib.table1 left 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.
>
>