How to Convert a Lateral View to a Table Reference

BennyBoyW
New Contributor III

‎03-30-2026 03:21 AM

Hi All

I have a view creation script in DataBricks which uses a lateral view to access columns in a structure held within an array field. 

It is working fine but I have noted that the LATERAL VIEW is now depracated and that I should be using a TABLE REFERENCE instead. 

I just cannot get the command to work however, so my question is - how do I convert the following to a TABLE REFERENCE ?

CREATE OR REPLACE VIEW newview
  AS
    SELECT
      t1.field1
      , structureitem.field2
      , structureitem.field3
    FROM table1 AS t1, table2 AS t2
    LATERAL VIEW EXPLODE(t1.structure) AS structureitem
    where t1.id = t2.id;
Labels:
0 Kudos

saurabh18cs
Honored Contributor III

‎03-30-2026 03:47 AM

Hi  try this  

CREATE OR REPLACE VIEW newview AS
SELECT
  t1.field1,
  structureitem.field2,
  structureitem.field3
FROM table1 AS t1
JOIN table2 AS t2
  ON t1.id = t2.id
JOIN LATERAL TABLE(explode(t1.structure)) AS structureitem
  ON TRUE;

 

BennyBoyW
New Contributor III
In response to saurabh18cs

‎03-30-2026 04:33 AM

Hi Again. Thank you for the extremely quick reply !

Unfortunately DataBricks doesn't seem to like the syntax above and returns the error :

[UNRESOLVABLE_TABLE_VALUED_FUNCTION] Could not resolve TABLE to a table-valued function. Please make sure that TABLE is defined as a table-valued function and that all required parameters are provided correctly. If TABLE is not defined, please create the table-valued function before using it.

0 Kudos

balajij8
Contributor III

‎03-30-2026 08:32 AM

You can use

CREATE OR REPLACE VIEW newview
  AS
    SELECT
      t1.field1,
      item.field2,
      item.field3
    FROM table1 AS t1
    INNER JOIN table2 AS t2 ON t1.id = t2.id
    , LATERAL EXPLODE(t1.structure) AS structureitem(item)

View solution in original post

BennyBoyW
New Contributor III
In response to balajij8

‎03-31-2026 04:52 AM

 - That's it !!!!! 
Thank you so much 😁