RE: Best approach for Embedded SQL SELECT in V7R1

Michael,
Maybe you are not paying enough attention to error messages:

SQL0511
Message Text: FOR UPDATE clause not valid.
Cause Text:
The FOR UPDATE clause cannot be used for cursor &1 because the result table is read only.
The result table is read only if:
1) The select-statement includes the DISTINCT keyword, an aggregate function, a GROUP
BY clause, a HAVING clause, or a UNION, EXCEPT, or INTERSECT operator.
2) The select-statement contains a VALUES clause.
3) The FROM clause identifies more than one table, more than one view, or a read-only
view.
4) The FROM clause contains a data change table reference.

Recovery Text: Cursor &1 cannot be updated. Remove the FOR UPDATE clause.


SQL0312
Message Text: Variable &1 not defined or not usable.
Cause Text:
The variable &1 appears in the SQL statement, but one of the following conditions exists:
1) No declaration for the variable exists or the declaration is not within the current scope.
2) The attributes are not correct for the use specified.
3) The host variable was specified in dynamic SQL. Host variables are not valid in dynamic
SQL.
4) In REXX, host variable names cannot contain embedded blanks.
5) The variable name is used in the routine body of an SQL procedure or function, but the
variable is not declared as an SQL variable or parameter. The scope of an SQL variable is
the compound statement that contains the declaration.
6) The variable is used in the routine body of an SQL trigger, but the variable is not
declared as an SQL variable or the variable is an OLD transition variable and cannot be
modified.
7) The variable is a transition variable in an AFTER trigger and is used in statement where
the variable could be modified. Modifying transition variables in AFTER triggers is not
allowed.

Recovery Text:
1) Verify that &1 is spelled correctly in the SQL statement.
2) Verify that the program contains a declaration for that variable. In RPG, the variable
must be declared globally or in the scope where it is used.
3) Verify that the attributes of the variable are compatible with its use in the statement.
4) Use parameter markers in dynamic SQL instead of host variables.
5) Remove embedded blanks from REXX host variable names.
6) Declare the variable as an SQL variable or parameter in the SQL procedure or function.
7) Declare the variable as an SQL variable or specify a NEW transition variable when the
variable is modified in an SQL trigger.
8) Remove the transition variable from the statement. Copying the transition variable to a
local variable and then using the local variable in the statement is also acceptable.

At my end, there are two problems:
1) I am at work
2) I have very little info about what you are doing

So, please try:
1) To read the manual(s)
2) Break the sql statement into something simpler - maybe
just try to fetch the first column or two.

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

Well, this is interesting. I get an SQL0511 with this statement:

Fetch C1 For :Maxrows Rows Into :Wkinvmstp;

and an SQL0312 with this statement:

Fetch C1 Into :Wkinvmstp;


On Tue, Nov 6, 2012 at 1:52 PM, Gary Thompson <gthompson@xxxxxxxxxxx> wrote:

Michael, another thought:

Maybe position 45 maps to a null capable field ?

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

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