Thanks Chuck. This is now on my list of things to play with when I get some time.
-Kurt
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Thursday, March 28, 2013 1:53 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Stored Procedure with Result Set
On 26 Mar 2013 14:48, Anderson, Kurt wrote:
<<SNIP>> I'm trying to switch the stored procedure to return the
SmallInt value as a single result set. <<SNIP>>
DECLARE rsCursor CURSOR FOR SELECT rtnTracking FROM sysibm/sysdummy1;
OPEN rsCursor; <<SNIP>>
rtnTracking is a local variable:
Declare rtnTracking SmallInt;
<<SNIP>> I hope there's a better way to set a local variable to a
result set w/o doing a "fake" select.
<<SNIP>> I'm at IBM i 7.1
A derived-table [Nested Table Expression (NTE)] can be defined as a /fullselect/ which can be defined by a VALUES-clause [with a column-list to name the columns]. The VALUES-clause can name an expression, and AFaIK a variable name should qualify as an expression in that context.
Thus I believe the following SELECT statement could eliminate the explicit reference to the SYSDUMMY1 [irrespective of the actual implementation, which on older releases, might be via the equivalent one-row TABLE named QSQPTABL]:
DECLARE rsCursor CURSOR FOR
SELECT myNTE.rtnTracking
FROM table( values(rtnTracking) ) as myNTE ( rtnTracking )
;
That may or may not qualify as having eliminated a "fake" SELECT to enable setting of "a local variable to a result set". However I presume that the reference to SYSDUMMY1 is the concern\issue, because the CURSOR is still going to need to be a SELECT statement.?
FWiW: Or, to avoid [possible confusion for] multiple references to the same apparent variable name [i.e. per using rtnTracking as both variable name and as a column identifier], perhaps instead of the prior declared SELECT statement to define the CURSOR, use this one:
DECLARE rsCursor CURSOR FOR
SELECT myNTE.myTracking
FROM table( values(rtnTracking) ) as myNTE ( myTracking )
;
Note: The parentheses for the values-clause are apparently optional for a single-column row-expression, though I do not recall ever trying that. Thus the following should be the equivalent to the above:
DECLARE rsCursor CURSOR FOR
SELECT myNTE.myTracking
FROM table( values rtnTracking ) as myNTE ( myTracking )
;
FWiW: When the parentheses are included inside of the TABLE(), the appearance is that of a table-function named VALUES having one argument [the variable rtnTracking] being passed, although I presume the SQL would infer that the intention is for a values-clause for one-row of the one-column from the context of the specification. I can not test that, as I only have v5r3. Or it is possible [but IMO unlikely] that the NTE specification would need to be specified as only the parenthesis; i.e.
omitting the word "table" in the above.?
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
