Re: Embedded SQL Problem

Thanks Vern,
    I'm still trying to learn SQL and that was a big lession. It worked
perfect.

Bob



On 11/29/06, vhamberg@xxxxxxxxxxx <vhamberg@xxxxxxxxxxx> wrote:

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.
>
--
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.