RE: Embedded SQL Problem

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?