Re: Values into Issue

Chuck,

Your FWiW are almost always worth a lot.
At least to me...

Thanks!



Bill





From: CRPence <crpbottle@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Date: 07/18/2016 11:35 AM
Subject: Re: Values into Issue
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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 ?';