Re: my first use of lateral

On 14 Dec 2012 17:16, Steve Richter wrote:

On Fri, Dec 14, 2012 at 3:28 PM, CRPence wrote:

select a.w1BOL, b.w2sdte
from w1mast a
, lateral ( select c.w2sdte
from w2mast c
where a.w1wacd = c.w2wacd and a.w1sid = c.w2sid
and c.w2sdte <> 0
fetch first row only ) b
where a.w1wacd = 'LAE' and a.w1BOL <> ''

I suspect the LATERAL specification is moot for the given SQL. I
expect that the /same/ SQL, but without the LATERAL keyword, will
function just fine; no errors, and the identical output\effect. It
is the NTE which allows the FETCH FIRST 1 ROW ONLY, not the use of
LATERAL.

thanks Chuck. I did not know about the NTE. In this case I am
referring to a.w1wacd and a.w1sid in the NTE. So LATERAL is
necessary.

Seems so obvious now, having been pointed out :-) I usually try to test before posting, but I knew v5r3 had some issues with the LATERAL, so I did not even try. I have since tested an equivalent statement, and the v5r3 performed properly for the LATERAL specification *and* properly diagnosed [with SQL5001] the reference to the correlation-name A.w1wacd when the LATERAL keyword was omitted. Apologies for [whatever caused; poor eyesight? ;-) ] my mis-read of the quoted SQL SELECT.