Re: Best approach for Embedded SQL SELECT in V7R1

Ok..based on the suggestions, I tried the MODS approach. I'm getting this
error:

SQL5011: Position 45 Host structure array WKINVMSTP not defined or not
usable.

On this statement:

Exec Sql
Fetch C1 For :Maxrows Rows Into :Wkinvmstp;

Where WkInvMstp is defined like this:

D WkInvmstp E DS EXTNAME(INVMSTP)
D Occurs(cMaxRows)

Are externally defined DS'es not valid for a FETCH statement. I didn't see
an externally described DS in any of the suggestions. Would I be better off
with an array? Could I reference an externally described DS with that?


On Tue, Nov 6, 2012 at 11:53 AM, Scott Mildenberger <
SMildenberger@xxxxxxxxxxxxxxxxxx> wrote:

Below are the basics of the approach. This is from a statistical
display comparing different trucks in our fleet. The user can sort them
by different statistics. I just have a function key that cycles through
the sort options. I just have the SFL build in one procedure and the
Sorting done in another one. Just call the build once based on the
user's criteria and then resort each time they hit the sort function
key. After resorting the data I clear and reload the subfile.

Data Structure definition (pretty sure it could be externally described)
d AllData ds dim(9999) qualified
d uaUnit like(uaUnit)
d s_NetRev like(s_NetRev)
d r_NetRev like(r_NetRev)
d ....Rest of fields omitted.


Fetch
Exec SQL Fetch from C2 for 9999 rows into :AllData;

Sort
// Sort the array according to the sort order.
select;
when SortOrder = 1;
sorta(d) %subarr(AllData(*).s_NetRev : 1 : AllDataRows);
SortedBy = 'Revenue';
SortOrder = 2;
when SortOrder = 2;
sorta(d) %subarr(AllData(*).s_TrpMiles : 1 : AllDataRows);
SortedBy = 'Trip Miles';
SortOrder = 3;
when SortOrder = 3;
sorta(d) %subarr(AllData(*).RPM : 1 : AllDataRows);
SortedBy = 'Revenue per Mile';
SortOrder = 4;
when SortOrder = 4;
sorta(a) %subarr(AllData(*).DhPct : 1 : AllDataRows);
SortedBy = 'Deadhead Percent';
SortOrder = 0;
other;
sorta(a) %subarr(AllData(*).uaUnit : 1 : AllDataRows);
SortedBy = 'Unit';
SortOrder = 1;
endsl;

Scott


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Tuesday, November 06, 2012 9:38 AM
To: RPG programming on the IBM i / System i
Subject: Re: Best approach for Embedded SQL SELECT in V7R1

Scott/Jon - Code samples for the DS array technique? Can I use an
externally described DS?


On Tue, Nov 6, 2012 at 11:32 AM, Jon Paris <jon.paris@xxxxxxxxxxxxxx>
wrote:

--
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.