Re: Embedded SQL Problem

["Bob Bledsoe" <bledsoe.bob@xxxxxxxxx>]

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.