RE: Multi-defined Fields: SQL Precompiler

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