RE: Multi-defined Fields: SQL Precompiler

[Jerry Adams <Jerry@xxxxxxxxxxxxxxx>]

Jeff,

That's happened to me often.  But the error that the precompiler issued was not very helpful.  It was only when I said, "To hell with it" and defined local variables for the SELECT, rather than use the subfile's field names, that the precompiler passed it on to the compiler.  The compiler is what notified me of the real error.

Now the program that that one is calling is crapping out.  Debug says the cursor for the SQL statement isn't open.  I've found a couple of errors, but still no luck.  I'll work on it for three days, like the last time, before I post anything here!  And, if I do, it'll be something stupid, again.

Jerry C. Adams
IBM System i Programmer/Analyst
--
B&W Wholesale
office: 615-995-7024
email:  jerry@xxxxxxxxxxxxxxx

Capital punishment is our society's recognition of the sanctity of human life. - Orrin Hatch

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Friday, April 30, 2010 12:35 PM
To: RPG programming on the IBM i / System i
Subject: Re: Multi-defined Fields: SQL Precompiler

Isn't it amazing how, once you post that you're stuck, the answer presents
itself?  :)


On Fri, Apr 30, 2010 at 1:02 PM, Jerry Adams <Jerry@xxxxxxxxxxxxxxx> wrote:

> Well, I found the answer - but it had nothing to do with multi-defined
> fields.
>
> In the $Process procedure, I had mis-typed the name of the subfile format.
>  Instead of ARQ028B, it should have been ARQ026B.
>
> Once I changed that, the program compiled (whether working or not remains
> an open question).
>
> Sorry to bother you on a Friday.
>
> Jerry C. Adams
> IBM System i Programmer/Analyst
> --
> B&W Wholesale
> office: 615-995-7024
> email:  jerry@xxxxxxxxxxxxxxx
>
> [I want to] make sure that everybody who has a job wants a job. - George
> H.W. Bush
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of Jerry Adams
> Sent: Friday, April 30, 2010 10:52 AM
> To: Midrange-RPG (rpg400-l@xxxxxxxxxxxx)
> Subject: Multi-defined Fields: SQL Precompiler
>
> Okay, I'm thoroughly confused.  I have an SQLRPGLE subfile program that, up
> until I tried to modify it, simply displayed a list of orders received today
> from a selected salesman.  The manager wanted to be able to display the
> order and its details (a reasonable request).
>
> I modified the subfile to have an option to select the order for detail
> display.  Then I modified the program to run through the subfile and display
> each order the client had checked.  However, the precompiler is choking now
> with: SQL0314  35     269  Position 48 Host variable CUST# not unique, for
> example; meaning that the field is multi-defined.
>
> BUT that ain't true.  The fields are only defined in the subfile record; I
> printed the precompiler listing to verify this.
>
> The SQL code that retrieves records for the subfile:
>
> IF  not eoj;
>  $SQLFormat();
>  EXEC SQL
>    Prepare MySQLStmt from :SQLString;
>  EXEC SQL
>    Declare OrderList Cursor for MySQLStmt;
>  EXEC SQL
>    Open OrderList;
>  DOW 1 = 1;
>
>    EXEC SQL
>      Fetch Next from OrderList
>        INTO :route, :cust#,:date,:timefull,:reference,
>          :Journal,:name,:address1,:address2,:city,
>          :state,:zip,:unused,:source;
>    IF  SQLState  = '02000'
>    OR  SQLCode   < *Zeros;
>      EXEC SQL
>        Close OrderList;
>      LEAVE;
>    ELSE;
>      (build the subfile record)
>
> The $SQLFormat procedure, which builds the SQLString is:
>
> SQLString = 'SELECT * FROM ' +
>            msifile + ' WHERE BNSM# = ' + route +
>            ' ORDER BY';
>
> SELECT;
>
>  WHEN  orderby = 'D';
>    sqlString = %trim(sqlstring)  +
>                ' BNDATE, BNTIME';
>
>  WHEN  orderby = 'N';
>    sqlString = %trim(sqlstring)  +
>                ' BNNAME, BNDATE, BNTIME';
>
>  OTHER;
>    sqlString = %trim(sqlstring)  +
>                ' BNCUST, BNDATE, BNTIME';
>
> ENDSL;
>
> sqlString = %trim(sqlstring)  +
>            ' For Read Only';
>
> The procedure that I added to run through the subfile ($Process) is:
>
> P $Process        B
>
> D                 PI
>
>  /free
>
>  FOR x = 1 to  #Limit;
>    CHAIN x ARQ028B;
>    IF  %found()  AND option  > *Blanks;
>      arq028(billstatus:cust#:reference:date:journal);
>    ENDIF;
>  ENDFOR;
>
>  RETURN;
>
> (ARQ028 is a separate program which displays the selected order; there is a
> prototype for it in the D-specs of this program.  )
>
> When I remove the $Process procedure, its prototype, and invocation, (no
> other changes) the program compiles.
>
> I do not understand why I am getting the error.
>
> Thanks.
>
> Jerry C. Adams
> IBM System i Programmer/Analyst
> --
> B&W Wholesale
> office: 615-995-7024
> email:  jerry@xxxxxxxxxxxxxxx
>
> I tell you, it's Big Business.  If there is one word to describe Atlantic
> City, it's Big Business.  Or two words - Big Business. - Donald Trump
>
> --
> This is the RPG programming on the IBM i / System i (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 IBM i / System i (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.