RE: Multi-defined Fields: SQL Precompiler -- RPG400-L

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.