× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.