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