How to joining table while using flatten logic in snowflake

Question

I have used lateral flatten logic in Snowflake in the below query. The query works up to alias A.

I'm getting invalid identifier error when I use the join condition based on the common column. ON B.product_id=A.product_id

SELECT A.ID, INDEX, purchase_list,
CASE WHEN INDEX = 1 and purchase_list NOT IN('121=find-values','122=find_results','123=item_details','',' ')
THEN purchase_list END as item_no
FROM (SELECT ID,index,d.value::string AS purchase_list FROM (
        SELECT ID,c.value::string AS purchase_list
        FROM table_1,lateral flatten(INPUT=>split(po_purchase_list, '|')) c
        ), 
        LATERAL flatten(INPUT=>split(purchase_list, ';')) d
      ) A  -- The query would be correct till here
      JOIN 
      table_2 B -- This is the table I need to join with table_1 
      ON B.product_id=A.product_id
      WHERE date='2022-03-03'
      AND b.item_src NOT IN('0','1','3','4')

Answer 1

from the error message it looks like it is not able to find the column product_id in one of the tables or CTE , can you include the column product_id in your SELECT statement for table A, and see if it works.