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



Let me restate this.  If I remember correctly, a join will not display
records that don't have matches in the other table... however a left join
will.


Notice that I added the group by statement.


 C/EXEC SQL
 C+ declare c1 cursor for
 C+ Select csnumb1, cat1, Sum(amt1), Sum(Amt2)
 C+ from mylib.table1 left join mylib.table2 on
 C+ table1.csnumb1=table2.csnumb2 and table1.cat1=table2.cat2
 C+ Group by csnumb1, cat1
 C/END-EXEC

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:14          Re: embedded SQL....                
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
              RPG programming                                              
              on the AS400 /                                               
                  iSeries                                                  
             <rpg400-l@midrang                                             
                  e.com>                                                   
                                                                           
                                                                           




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

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.