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



What you really want is a "full outer join", but as of yet AS/400 does not 
support it.

This is all you have to do.

Select csnumb1, cat1, amt1, amt2
from mylib.table1 left outer join mylib.table2 on 
 table1.csnumb1=table2.csnumb2 and table1.cat1=table2.cat2
union
Select csnumb2, cat2, 0, amt2
from mylib.table2 exception join mylib.table1 on 
 table2.csnumb2=table1.csnumb1 and table2.cat2=table1.cat1
order by 1, 2

So you get all of table1 whether there is a match in table 2 or not, then 
you union all of table 2 that does not have a match in table1.  I put a 
zero in place of amt1, to emphasize that there will never be a non-null 
value for amt1.

Mark


----- Message from "Jake M" <jakeroc@xxxxxxxxx> on Fri, 14 Apr 2006 
13:53:59 -0500 -----
To:
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Subject:
embedded SQL....
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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


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.