Re: can lateral be made to function like left outer join?

[CRPence <CRPbottle@xxxxxxxxx>]

On 19 Feb 2013 09:24, Steve Richter wrote:
> <<SNIP>>
> ( I want to use lateral because lateral supports "fetch first row only" )

  AFaIK the Nested Table Expression (NTE) or Derived Table supports the 
FETCH FIRST clause; i.e. LATERAL need not be specified for the NTE.

> select  a.srcdta, b.ibmreqd
> from    qrpglesrc a
> , lateral ( select  c.ibmreqd from sysibm/sysdummy1 c
>              where   c.ibmreqd = 'N' ) b

  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