RE: Best approach for Embedded SQL SELECT in V7R1

[Gary Thompson <gthompson@xxxxxxxxxxx>]

Michael,
I have something similar in daily use:

      * define Host Variables for all fields in each file
     D AM01REC       E DS                  EXTNAME (AM01)
     D                                     prefix  (am_)                        Article Master

     C/EXEC SQL
     C+ FETCH C1 INTO :AM01rec, :wa_DELLOC
     C/END-EXEC

I think the problem is a miss-match between the data type of the
column returned by your sql statement and the DS definition

Not >how< the DS definition was created
 

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