Re: Embedded SQL Problem

Thanks Rob. Now I understand. I thought they were comments.

On 11/30/06, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote:

Bob,

It confused me at first also, but those weren't just comments he put way
off to the right.

C+   from "INNOPFLS"/"BUILD00001" A   inner join
C+         "INNOPFLS"/"CLASS00001" B on (A.CLASS00001 = B.CLASS00001)
left outer join
C+         "INNOPFLS"/"CONTRACTOR" C on (A.PRIMA00001 = C.CONTR00001)
inner join
C+         "INNOPFLS"/"CONTACT" D    on (C.CONTACT_ID = D.CONTACT_ID)
inner join
C+         "INNOPFLS"/"BUSINESS" E   on (D.BUSIN00001 = E.BUSIN00001)
inner join


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Bob Bledsoe" <bledsoe.bob@xxxxxxxxx>
Sent by: rpg400-l-bounces+rob=dekko.com@xxxxxxxxxxxx
11/29/2006 06:35 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Re: Embedded SQL Problem






Eric,
  I don't see what makes the second join an outer join. Is it just the
position?


On 11/29/06, DeLong, Eric <EDeLong@xxxxxxxxxxxxxxx> wrote:
>
> Hi Bob,
>
> In your select, you use the simple join syntax, where all your join
> criteria are within your where clause.  This form of join is implemented
as
> an INNER join, which requires a matching row in both tables to be
> returned.  What you want to use for the CONTRACTOR file is a LEFT OUTER
> JOIN.  I also recommend that you get in the habit of using the formal
join
> syntax, where join style and criteria are specified explicitly....
>
> C/exec sql
> C+  Declare C1 cursor for
> C+  Select A.BUILD00002, A.CAPTU00001, B.NAME, G.VALUE, H.DESCR00001,
> C+         A.WORK_00001, E.NAME, A.SUBMI00001
> C+   from "INNOPFLS"/"BUILD00001"
> A                                             inner join
> C+         "INNOPFLS"/"CLASS00001" B on (A.CLASS00001 = B.CLASS00001)
> left outer join
> C+         "INNOPFLS"/"CONTRACTOR" C on (A.PRIMA00001 = C.CONTR00001)
> inner join
> C+         "INNOPFLS"/"CONTACT" D    on (C.CONTACT_ID = D.CONTACT_ID)
> inner join
> C+         "INNOPFLS"/"BUSINESS" E   on (D.BUSIN00001 = E.BUSIN00001)
> inner join
> C+         "INNOPFLS"/"BPATT00001" F on (A.BUILD00001 = F.BUILD00001)
> inner join
> C+         "INNOPFLS"/"ATTRI00001" G on (F.ATTRI00001 = G.ATTRI00001)
> inner join
> C+         "INNOPFLS"/"ATTRIBUTE" H  on (G.ATTRI00002 = H.ATTRI00001)
> C+   where A.CAPTU00001 between :Stdnumt and :Endnumt
> C+  Order by A.BUILD00002
> C/end-exec
>
> hth,
> Eric
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Bob Bledsoe
> Sent: Wednesday, November 29, 2006 3:31 PM
> To: RPG400-L@xxxxxxxxxxxx
> Subject: Embedded SQL Problem
>
>
> I have the following embedded SQL that works well as far as it goes.
>
>
> C/exec sql
> C+  Declare C1 cursor for
> C+  Select A.BUILD00002, A.CAPTU00001, B.NAME, G.VALUE, H.DESCR00001,
> C+         A.WORK_00001, E.NAME, A.SUBMI00001
> C+   from "INNOPFLS"/"BUILD00001" A,
> C+         "INNOPFLS"/"CLASS00001" B,
> C+         "INNOPFLS"/"CONTRACTOR" C,
> C+         "INNOPFLS"/"CONTACT" D,
> C+         "INNOPFLS"/"BUSINESS" E,
> C+         "INNOPFLS"/"BPATT00001" F,
> C+         "INNOPFLS"/"ATTRI00001" G,
> C+         "INNOPFLS"/"ATTRIBUTE" H
> C+   where A.CAPTU00001 between :Stdnumt and :Endnumt
> C+     and  A.CLASS00001 = B.CLASS00001
> C+     and  A.PRIMA00001 = C.CONTR00001
> C+     and  C.CONTACT_ID = D.CONTACT_ID
> C+     and  D.BUSIN00001 = E.BUSIN00001
> C+     and  A.BUILD00001 = F.BUILD00001
> C+     and  F.ATTRI00001 = G.ATTRI00001
> C+     and  G.ATTRI00002 = H.ATTRI00001
> C+  Order by A.BUILD00002
> C/end-exec
>
> The problem comes in because some of the primary records don't have a
> record
> in the CONTRACTOR file. When that happens I would like to use the info
> from
> a different file. I tried adding the other file and then using an OR
> before
> the Order by but that just put it into a loop. What is the correct way
to
> handle this?
> --
> 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.