f



SQL lateral vs joining to a sub select

I have two tracking number files. The 2nd has more info that the first. But=
 it has multiple records for each tracking number.  So I want to join to a =
select which uses "fetch first row only" to give me a one for one join. Pro=
blem is SQL gave me an error.  I am using the tracknbr key from the first f=
ile in the WHERE clause of the SELECT from the 2nd file.   SQL says that co=
lumn does not exist.

select   a.tracknbr, e.*                                     =20
from       qtemp/steve1 a                                    =20
left outer join ( select d.utsvctype, d.utpkchg, d.utpktype, =20
                  d.utpkwgt                                  =20
                  from   zuptrcv2 d                          =20
                  where  d.utpktrack =3D a.tracknbr            =20
             fetch first row only ) e    on 1 =3D 1            =20
Column or global variable TRACKNBR not found.                =20

So I switch the SQL to use LATERAL and the statement works.  Why would SQL =
have a problem with joining to a sub select that uses a column from the fir=
st file?

select   a.tracknbr, e.utsvctype, e.utpkchg                    =20
from       qtemp/steve1 a  ,                                   =20
  lateral       ( select d.utsvctype, d.utpkchg, d.utpktype,   =20
                  d.utpkwgt                                    =20
                  from   zuptrcv2 d                            =20
                  where  d.utpktrack =3D a.tracknbr              =20
             fetch first row only ) e                          =20
SELECT statement run complete.                                 =20
0
Steve
7/28/2016 4:14:30 PM
comp.sys.ibm.as400.misc 9219 articles. 4 followers. Post Follow

2 Replies
795 Views

Similar Articles

[PageSpeed] 54

this author is terrific
http://www.itjungle.com/fhg/fhg030514-printer01.html

I was not aware I can use "lateral" on the "left outer join". This statement gives me what I am looking for:

select   a.tracknbr, coalesce(e.utsvctype,' ') utsvctype          
from       qtemp/steve1 a                                         
left outer join lateral                                           
( select d.utsvctype, d.utpkchg, d.utpktype,                      
                  d.utpkwgt                                       
                  from   zuptrcv2 d                               
                  where  d.utpktrack = a.tracknbr                 
             fetch first row only ) e    on 1 = 1                 


just do not understand why SQL has a problem with the join when lateral is not used.

0
Steve
7/28/2016 4:57:12 PM
On 28-Jul-2016 11:14 -0500, Steve Richter wrote:
> I have two tracking number files. The 2nd has more info that the
> first. But it has multiple records for each tracking number. So I
> want to join to a select which uses "fetch first row only" to give me
> a one for one join. Problem is SQL gave me an error. I am using the
> tracknbr key from the first file in the WHERE clause of the SELECT
> from the 2nd file. SQL says that column does not exist.
>
>  select   a.tracknbr, e.*
>  from       qtemp/steve1 a
>  left outer join ( select d.utsvctype, d.utpkchg
>                         , d.utpktype,  d.utpkwgt
>                    from   zuptrcv2 d
>                    where  d.utpktrack = a.tracknbr
>               fetch first row only ) e    on 1 = 1
> Column or global variable TRACKNBR not found.
>
> So I switch the SQL to use LATERAL and the statement works. Why
> would SQL have a problem with joining to a sub select that uses a
> column from the first file?
>
> select   a.tracknbr, e.utsvctype, e.utpkchg
> from       qtemp/steve1 a  ,
>    lateral       ( select d.utsvctype, d.utpkchg
>                         , d.utpktype,  d.utpkwgt
>                    from   zuptrcv2 d
>                    where  d.utpktrack = a.tracknbr
>               fetch first row only ) e
> SELECT statement run complete.

   The LATERAL keyword informs the SQL that there is an intention to 
_make visible_ the columns from the /lateral table-reference/.  FWiW, 
the TABLE keyword happens to enable the same.  Either keyword must be 
specified explicitly, to make the column references visible.  Thus 
either of the following revisions to the first query would allow the 
query to run:

     select   a.tracknbr, e.*
     from       qtemp/steve1 a
     left outer join
         table  ( select d.utsvctype, d.utpkchg
                       , d.utpktype,  d.utpkwgt
                  from   zuptrcv2 d
                  where  d.utpktrack = a.tracknbr
                  fetch first row only
                ) e
       on 1 = 1


     select   a.tracknbr, e.*
     from       qtemp/steve1 a
     left outer join
         lateral( select d.utsvctype, d.utpkchg
                       , d.utpktype,  d.utpkwgt
                  from   zuptrcv2 d
                  where  d.utpktrack = a.tracknbr
                  fetch first row only
                ) e
       on 1 = 1


-- 
Regards, Chuck
0
CRPence
7/28/2016 6:10:34 PM
Reply: