Re: Embedded SQL Problem

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Glad it worked, Bob - very cool!

Vern

At 05:29 PM 11/29/2006, you wrote:

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