Big Query Cast on JOIN-CodePudding

I'm joining two tables that are pretty simple, but I need to cast and I'm finding some challenges

Name product_id_INT64 not found inside A at [5:80]

The query is as follows

select 
cast (A.product_id AS INT64) as product_id_INT64, 
A.visit_start_date 
from `project.user.tableA` as A 
inner join `project.feed.20210816` as B on A.product_id_INT64 = CAST(cat.PARENT_ID AS INT64)

what is the better way to do this? Any pointers?

CodePudding user response：

To solve this problem you can use two methods:

Create temp view of the first table (here i call it tab1) then call it in the join :

WITH TAB1 AS 
(select 
cast (product_id AS INT64) as product_id_INT64, 
visit_start_date 
from `project.user.tableA`
)
SELECT * FROM 
TAB1 inner join `project.feed.20210816` as B on TAB1.product_id_INT64 = CAST(B.PARENT_ID AS INT64)

Cast directly in while joining :

select 
cast (A.product_id AS INT64) as product_id_INT64, 
A.visit_start_date 
from `project.user.tableA` as A 
inner join `project.feed.20210816` as B on CAST(A.product_id AS INT64)= CAST(B.PARENT_ID AS INT64)

CodePudding user response：

Use below instead

select 
  cast (a.product_id as int64) as product_id_int64, 
  a.visit_start_date 
from `project.user.tablea` as a 
inner join `project.feed.20210816` as b 
on cast(a.product_id as int64) = cast(cat.parent_id as int64)