Issue with subquery - All expressions must have explicit name

Question

Not sure what is going on here and why this is not working. I'm receiving the following error:

"All expressions in a derived table must have an explicit name" - working with teradata.

    select clm.c_clm
    ,clm.c_loc
    from 
    (select *
    from pearl_p.TLTC900_CLM clm) as cl
    left join
    (select 
    max(av.d_usr_udt_lst)
    from pearl_p.TLTC913_AVY av
    group by 1) as avy
    on cl.i_sys_clm = avy.i_sys_clm

Answer 1

Your max(av.d_usr_udt_lst) in your subquery doesn't have an explicit name. You need to alias it like this:

max(av.d_usr_udt_lst) as "MaxThing"

So the query looks like

select clm.c_clm
    ,clm.c_loc
    from 
    (select *
    from pearl_p.TLTC900_CLM clm) as cl
    left join
    (select 
    max(av.d_usr_udt_lst) as "MaxThing"
    from pearl_p.TLTC913_AVY av
    group by 1) as avy
    on cl.i_sys_clm = avy.i_sys_clm

Answer 2

Apart from that error, you have another error in your join:

select clm.c_clm, clm.c_loc
from (select *
      from pearl_p.TLTC900_CLM clm
     ) cl left join
     (select max(av.d_usr_udt_lst)
      from pearl_p.TLTC913_AVY av
      group by 1
     ) as avy
    on cl.i_sys_clm = avy.i_sys_clm
--------------------------^ This variable is not defined.

I think you might want something like:

select clm.c_clm, clm.c_loc
from (select *
      from pearl_p.TLTC900_CLM clm
     ) cl left join
     (select i_sys_clm, max(av.d_usr_udt_lst) as maxdate
      from pearl_p.TLTC913_AVY av
      group by i_sys_clm
     ) avy
    on cl.i_sys_clm = avy.i_sys_clm and
       cl.<date column goes here> = avy.maxdate

Answer 3

The Alias clm only exists within the sub-query that defines the cl alias. As such you cannot call clm outside of that sub-query. Change all the outer references to cl adn you will be fine. While your at it, you should also gt rid of the SELECT * and explicitly identify the colums you need.