(no subject)

[Alan Campin <Alan.Campin@xxxxxxx>]

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;