Re: Best approach for Embedded SQL SELECT in V7R1

[Michael Ryan <michaelrtr@xxxxxxxxx>]

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.