BigQuery SQL Cast error: No matching signature for operator = for argument types: STRING, INT64. Supported signature: ANY = ANY

Question

I'm getting this error while running a query in BigQuery in the part of 4th line (highlighted in italics). In this query, table 1 has a data field 'Lsc' as String data type and table 2 has a data field 'Sc' as Integer data type.

Query-

SELECT safe_cast(a.Lsc as INT64), b.Sc FROM table1 as a LEFT JOIN table2 as b on a.Lsc = b.Sc

What am I doing wrong ?

I tried with Cast as well but same error. I also tried as below but error remains-

SELECT safe_cast(a.Lsc as INT64) as a.Sc, b.Sc FROM table1 as a LEFT JOIN table2 as b on a.Lsc = b.Sc

Answer 1

you can try this

SELECT safe_cast(a.Lsc as INT64) as Lsc, b.Sc
FROM table1 as a
LEFT JOIN table2 as b ON safe_cast(a.Lsc as INT64) = b.Sc

In your example in the JOIN you were comparing an integer with a string, that could be the reason for the error