Re: Embedded SQL Problem

["Bob Bledsoe" <bledsoe.bob@xxxxxxxxx>]

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.