On 18-Feb-2015 11:57 -0600, Sam_L wrote:
On 18-Feb-2015 10:46 -0600, Stone, Joel wrote:
The following SQL errors with
"Result of SELECT more than one row."
That would be sqlcode -811 or msg SQL0811 [in this case, rc2]
As often repeated [and seemingly never /sinking in/], messages should
be identified with the msgid [and the additional context from a joblog
often can be additionally helpful when describing an issue].
Identifying error messages solely with the text is unhelpful to anyone
looking for a possibly matching issue\concern, because the text is not
going to be the same spanning languages nor even necessarily the same
spanning releases.
This makes sense because there is more than one row returned by
the subselect.
How I can tell SQL to only use the FIRST row returned by the
subselect?
update rjsimage/docs00 a
set keyword3 =
( select first scaletix
from jstone/docs00fix2 b
where a.docid = b.docid
FETCH FIRST 1 ROW ONLY )
where exists
( select first scaletix
from jstone/docs00fix2 b
where a.docid = b.docid
FETCH FIRST 1 ROW ONLY )
The operating assumption is that token FIRST appearing in the
column\expression-list is not a variable or column name, and that is
instead a failed attempt at conjuring a keyword that would ask the SQL
to provide only the first row of the result set. As such, both
subselects are assumed to be the request: SELECT scaletix FROM ...
FWiW the subselect in the EXISTS predicate is already implicitly much
like having coded a FETCH FIRST ROW ONLY, because the mere existence of
any match qualifies the EXISTS to be true; for that reason there should
be no need to code that clause, and besides, that subquery in the EXISTS
would not be the origin for the -811.
I tried FETCH FIRST 1 ROW ONLY as below but it returned error
"Keyword FETCH not expected."
<<SNIP>>
The "below" was the same as the "above", so the former was snipped.
The support for the FETCH FIRST clause in a _subselect_ came with
v6r1 [aka IBM i 6.1].
<www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_61/db2/rbafzsubselect.htm>
Is the system being used perhaps still v5r4?
Maybe "select DISTINCT scaletix ..." would work and forget the FETCH
FIRST ...
Depending on the data, the DISTINCT *may* allow the request to
succeed. Yet selecting DISTINCT values would be capable of avoiding the
-811 error only *if* all values for field SCALETIX are identical for any
particular matching DOCID found in the fix-file. Otherwise the same
issue with multiple\non-distinct values would persist.
A possibly better [the safest] and at least a consistent\predictable
result could be had by requesting an aggregate result such as the MIN()
or MAX() value; i.e. the result of the aggregate expression would always
be the maximum or minimum value if the values were not all the same
across the rows with matching DOCIF values, and the max equals the min
when the values are all identical:
update rjsimage/docs00 a
set keyword3 =
( select MIN(scaletix)
from jstone/docs00fix2 b
where b.docid = a.docid
)
where exists
( select scaletix
from jstone/docs00fix2 b
where b.docid = a.docid
)
As noted earlier [doc link included], the SQL apparently was enhanced
to allow the FETCH FIRST and ORDER BY clauses to pare the result to just
one row for the scalar subselect used on the assignment; no such support
at v5r3, a test confirmed. The following message implies as much,
though no release was noted:
<
http://archive.midrange.com/midrange-l/201207/msg01057.html>]. In that
case, since v6r1 the ORDER BY with FETCH FIRST ROW should ensure
consistent\predictable results for paring any extra rows just as could
the aggregate expressions MIN() or MAX() that should be supported on
older releases, but an unordered set requesting just the FETCH FIRST ROW
ONLY would return any\an-unpredictable available value from the matching
rows:
update rjsimage/docs00 a
set keyword3 =
( select MIN(scaletix)
from jstone/docs00fix2 b
where b.docid = a.docid
/* order by scaletix */
/* uncomment ORDER BY for same as using MIN w/out FETCH FIRST */
fetch first row only /* or: fetch first 1 rows only */
)
where exists
( select scaletix
from jstone/docs00fix2 b
where b.docid = a.docid
) -- should be no need for a FETCH FIRST or ORDER BY here
midrange.com
