Invalid table alias or column reference 'SYNTHJOIN_xxxxx'

3
votes

would appreciate a few extra set of eyes; I cannot figure out for the life of me why this query won't work, I'm converting Teradata SQL scripts to Hive.

        Select a.some_id FROM app_table_dc.seds_thingy_mapping_seds_vdata   e
         LEFT JOIN app_table_dc.assets a
            ON e.eng_serial_number = a.serial_number
            AND a.asset_type_id = 2
      LEFT JOIN (SELECT vdata_thingy_id
                                        , thingy_date InductDate
                                    FROM app_table_dc.seds_thingy_mapping_seds_vdata
                                    WHERE thingy_type = 'EI') ei

   ON ei.vdata_thingy_id = e.vdata_thingy_id
     JOIN app_table_dc.engine_type_asset_models am
        ON a.asset_model_id = am.asset_model_id
     JOIN app_table_dc.engine_type egt
        ON am.engine_type_id = egt.engine_type_id

The last two lines do NOT work; when I take them out, it'll run...However with them included, I end up with the error logs to show :

Error Code: 10004, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 1:4915 Invalid table alias or column reference 'SYNTHJOIN_110d3abf': (possible column names are: blahblahblah)

I made sure the table names being referred to had the correct nomenclature/names, as well as the columns being referred to. I'm at my wits end.

Thank you for any insight in advance!

2
hadoophivesubqueryteradataalias
As per my understanding there is no issue with your query, would need some more details - last 2 lines refer to table (app_table_dc.engine_type) , are you able to query this table without any errors and is this an external/internal table with/without partitions - Vijiy
Hi, and yes, I can query this table with no issues, it is a very small associative entity table, with just 10 or so rows. It isn't transactional, I believe it's an internal table as should be the linking/joined table, No Partitions. Though theres 34 unique ids on the first table and like I said just 10 unique ids to map on the second.. Though I highly doubt that is the issue. - Waseem Mehar
Can you please check that column engine_type_id do exists in both table and with the same type? - Jainik
Yes, engine_type_id exists in both tables as BIGINT. Strange right? - Waseem Mehar

2 Answers

0
votes

--the code is right

--since I see this error :(possible column names are: blahblahblah)

Can you check if asset_model_id and engine_type_id are present in both the tables((a,am),(am,egt)) you are joining with.

May be the naming is different due to which it is throwing an error.

0
votes

This was the only recourse that worked:

    with somealias as (select * from app_table_dc.engine_type)
                Select a.some_id 
            FROM app_table_dc.seds_thingy_mapping_seds_vdata e 
            LEFT JOIN app_table_dc.assets a ON e.eng_serial_number = a.serial_number AND a.asset_type_id = 2 
            LEFT JOIN (SELECT vdata_thingy_id , thingy_date InductDate FROM app_table_dc.seds_thingy_mapping_seds_vdata WHERE thingy_type = 'EI' ) ei 
            ON ei.vdata_thingy_id = e.vdata_thingy_id 
            JOIN app_table_dc.engine_type_asset_models am ON a.asset_model_id = am.asset_model_id 
    JOIN somealias egt 
ON am.engine_type_id = egt.engine_type_id