RE: Best approach for Embedded SQL SELECT in V7R1

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.