Re: Best approach for Embedded SQL SELECT in V7R1

Not sure on your error but what if you make the data structure and array
instead of a MODS, just add the dim statement in place of the occurs.
Can't remember off-hand if you need the qualified also.

Scott


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Tuesday, November 06, 2012 11:26 AM
To: RPG programming on the IBM i / System i
Subject: 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.

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