On 19 Feb 2013 11:31, Steve Richter wrote:
On Tue, Feb 19, 2013 at 1:51 PM, CRPence wrote:
The following functions to get the results of the NTE using the
FETCH FIRST clause, as tested on v5r3:
SELECT a.srcdta, b.ibmreqd
from QRPGLESRC a
left outer join
(select * from sysibm/sysdummy1 where ibmreqd='X'
fetch first row only) b
on /* 1=1 or... */ srcseq>0
that is neat. I never tried to join to a subselect. And you are
right. you do not have to join using keys because there is only
1 row to join to.
Actually in that example there are zero rows to which the primary is
joined. I should have used the predicate 1<>1 on the WHERE-clause vs
ibmreqd='X' to make that more conspicuous. I had changed the constant
from 'N' in the original query to use the constant 'X' in the above
query, having known the idea behind IBMreqd was an answer to a question
[i.e. 'Y' or 'N' in English], but in hindsight I understand how easily
that could be missed.
The reason having specified two table-reference separated by a comma
in the FROM-clause of the original example produced no rows, is because
the implicit join is a CROSS JOIN; i.e. a Cartesian product. A product
for which a join of zero-to-many-rows to zero-rows is always an empty
set much like the product of anything multiplied by zero is always zero.
The result of a LEFT OUTER JOIN is always at least the full set of
the primary irrespective the number of rows in the secondary [as long as
selection does not limit the rows from the primary]. Thus the join
condition of "ON 1=1" [or for what should be the effectively identical
outcome under normal circumstances, using "ON srcseq>0"] gives all rows
from the primary with the NULL value for any "matching" rows in the
secondary just as it would for any non-matching rows; i.e. because there
are zero matching rows, a NULL row is generated to show the non-matching
condition, for each row of the primary. When there is one row in the
secondary, the result of the LEFT OUTER JOIN ON 1=1 will look the same
as the CROSS JOIN.