CODEWITHSUNDEEP
sqlgoogle-bigquery
disruptive
disruptive

Reputation: 5946

BQ join with casting on joined column

I've been fighting a difficult join with a column to join on stored as a float. Plus I need to join on the table name. I've kindly been steered in the right direction with the correct format, but I keep running into problems with the join. To clarify (don't ask me why, but the column entry of one (PARENT_ID) is a float). The data is historic and I cannot change these tables, but want to be able to select the appropriate table and join on the start_date and the product_id. To aim to make things tighter I have put casts on the fields. But I do not understand the source of the error.

    with tab1 as (
    select 
        cast(product_id as INT64) as product_id_64, 
        cast(FORMAT_DATE('%Y%m%d', CAST(start_date AS DATE)) as STRING) as start_date_string
        
    from `project.user.table`
    ) 

    select * EXCEPT(GTIN, SALE_PRICE) from `user2.dataset.*` b
    inner join tab1 
    on b._TABLE_SUFFIX = tab1.start_date_string
    and tab1.product_id_64 = cast(b.PARENT_ID as INT64)

I then get the following error come up: Cannot read field of type FLOAT64 as STRING Field: PARENT_ID

Upvotes: 0

Views: 411

Answers (1)

Raul Saucedo
Raul Saucedo

Reputation: 1780

Unfortunately, you can’t use in this query the command _TABLE_SUFFIX, because the limitation is that you cannot compare a column with the same name and one of them has different types. You will get this error(Cannot read field of type FLOAT64 as STRING Field: PARENT_ID).

If a single scanned table has a schema mismatch (that is, a column with the same name is of a different type), the query fails with the error Cannot read field of type X as Y Field: column_name.

You can see all the limitations in this document.

Upvotes: 1

Related Questions