Re: Embedded SQL Problem

[vhamberg@xxxxxxxxxxx]

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.