× 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 18-Jul-2016 09:00 -0500, broehmer wrote:
I find it perplexing that I can't find any example of how
this should be set up.

FWiW:

->Database->Reference->SQL reference->Statements->VALUES INTO
[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzvalinto.htm]
"The VALUES INTO statement produces a result table consisting of at most one row and assigns the values in that row to variables. …

Syntax diagram

.-,--------.
V |
>>-VALUES--+-+-expression-+---------------+--INTO----variable-+-><
| '-NULL-------' |
| .-,--------------. |
| V | |
'-(--+---+-expression-+-+-+--)-'
| '-NULL-------' |
'-row-fullselect-----'

…"

->Database->Reference->SQL reference->Queries->fullselect
[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzmark.htm]
"The fullselect is a component of the select-statement,…"

The opening text in that link is very poor wording IMO. Older docs suggest directly and simply, what I feel is much clearer, that "A fullselect specifies a result table." The newer docs do retain that same text as explanation, but only later, after both that opening "The fullselect is …" and the syntax diagram:

"• A fullselect that is enclosed in parenthesis is called a subquery. For example, a subquery can be used in a search condition.

• A scalar-fullselect is a fullselect, enclosed in parentheses, that returns a single result row and a single result column. If the result of the fullselect is no rows, then the null value is returned. An error is returned if there is more than one row in the result.

• A fullselect specifies a result table. If UNION, EXCEPT, or INTERSECT is not used, the result of the fullselect is the result of the specified subselect or values-clause."

->…->Queries->fullselect->Examples of a fullselect
[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzfullselectex.htm]


Not that I haven't looked but "values" is so generic that trying to
find an SQL/ILE example isn't easy.

Locating *any* specific example of SQL embedded within ILE RPG is, in my experience, somewhat difficult, especially within the IBM docs; best to search the web more generally, outside of the [ ;-) lack-of-]KnowledgeCenter.

While /generic/ in capability, consider that for the specific syntax of a row-fullselect as [a mundane] SELECT-statement, the question in the OP about where does the WHERE clause go within the VALUES INTO statement, could have been answered legitimately [even if sounding somewhat facetious] with just "The same place a WHERE clause would go in any other select-statement":

VALUES ( select-statement ) INTO variables

FWiW, that is why I had reformatted the string-assignment given in the OP, into the following [quoted immediately below], from the original [also follows, quoted just below that]. The hope, that the alignment of the clause keywords would render more conspicuous, the required placement of the logically-next [i.e. the where-]clause in the statement:

Reformatted:

strsql = 'Values (Select Count(*) ' +
'From ' + %trim(filein) +
') into ?';

vs the Original:

strsql = 'Values (Select Count(*) ' +
'From ' + %trim(filein) + ') into ?';


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

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.