Re: Library List in SQL Stored Procedures - Was Re: (no subject)

Hi Alan,

One possible solution might be to start with a SET OPTION NAMING = *SYS
followed by a call to QCMDEXC that does a CHGLIBL to the required library
list.

HTH

Paul


----- Original Message -----
From: "Alan Campin" <Alan.Campin@xxxxxxx>
To: <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, July 26, 2005 11:05 PM
Subject: (no subject)


> Using RUNSQLSTM and using option *SYS for naming convention. Makes no
difference. Tried to use *SQL but same thing.
> >> Hi Alan,
>
> >> My understanding is that PATH is applied when calling an unquailified
> >> procedure or UDF. You need to specify a SCHEMA for unquailifed tables
or
> >> views - which, of course, restricts you to one default library.
>
> >> The library list is only used if you are using a system naming
convention
> >> (as on STRSQL, JDBC connection, pre-compiler options) but I don't think
you
> >> have that option when you are creating a true SQL procedure.
>
> >> HTH
>
> >> Paul
>
> >> ----- Original Message -----
> >>From: "Alan Campin" Alan.Campin@xxxxxxx>
> >>To: midrange-l@xxxxxxxxxxxx>
> >> Sent: Monday, July 25, 2005 9:59 PM
> >> Subject: Library List in SQL Stored Procedures
>
>
> > I am trying to write a SQL Stored Procedure but am having no luck
getting
> to work. I have written them before and this smells like another complier
> bug.
> >
> > Each time that I try to run this stored procedure, I keep getting a
> message saying that everyone of the tables is not found in QGPL if I use
> QGPL as the default collection and my library MP1CAMPIA if I do not
specify
> a default collection. All the libraries are on the library list as well as
> being added to Set Path statement but it keeps trying to find them in the
> default collection. What gives? This is driving me crazy. If you don't
> specify a collection, doesn't it use the library list?
> >
> > Anyway thanks.
> >
> >     SET DTA_LIBRARY = 'A1' CONCAT INCOMPANYCODE CONCAT 'DTA' ;
> >     SET AMO_LIBRARY = 'A1AMODTA';
> >
> >     SET PATH DTA_LIBRARY, AMO_LIBRARY;
> >
> >     SET STMT = 'CRTDUPOBJ OBJ(QAQQINI) FROMLIB(MP1CAMPIA)
> >                   OBJTYPE(*FILE) TOLIB(QTEMP) DATA(*YES) ' ;
> >     SET STMTLENGTH = LENGTH ( STMT ) ;
> >     CALL QSYS/QCMDEXC ( STMT , STMTLENGTH ) ;
> >
> >     Update QTEMP/QAQQINI
> >        SET QQVAL = '*YES' WHERE QQPARM = 'FORCE_JOIN_ORDER' ;
> >
> >     SET STMT = 'CHGQRYA QRYOPTLIB(QTEMP)' ;
> >     SET STMTLENGTH = LENGTH ( STMT ) ;
> >     CALL QSYS/QCMDEXC ( STMT , STMTLENGTH ) ;
> >
> >     Sel: Begin
> >       Declare c1 Cursor With Return For
> >         Select RFPAYC,
> >                RFRFNN,
> >                RFDATE,
> >                RFAMT,
> >                CMPCMPY,
> >                CMPNAME
> >         From OPMRFPF1
> >           Inner Join OPMHDRF1
> >             On RFORDN = HDORDN
> >           Inner JOIN MFMDIVF1
> >             On HDODIV = DIVDIV
> >           Inner JOIN MFMCMPF1
> >             On DIVCMPY = CMPCMPY
> >         Where RFRFNN <> 0 And
> >               RFDATE Between InFromDate and InToDate
> >         Order By CMPCMPY;
> >         Open c1;
> >       End Sel;
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>