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.