Re: Embedded SQL Problem

Probably a UNION SELECT is what you want

SELECT ... FROM
UNION
SELECT ... FROM

HTH
Vern
-------------- Original message -------------- 
From: "Bob Bledsoe" <bledsoe.bob@xxxxxxxxx> 

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.