cannot access field on a value with type string in google bigquery

Consider the example query:

SELECT s.pm_station, s.RH, (CASE 
                        WHEN s.wind_direction="W" then (SELECT ss.W FROM 
                       `Airnow_dataset.station_neighbour` ss 
                        WHERE ss.pm_station=s.pm_station ) 
                        ELSE "na" 
                        END) as neighbour_wind_direction
FROM `Airnow_dataset.adjustedTime_met_la_wind_letter` s

BigQuery throws the below error:

\n

cannot access field pm_station on a value with type string at [3:83]

\n

Solution:

Never use the table alias name same as one of its column name

The problem with this query is that your station_neighbour table has a column named S, so the outer alias for adjustedTime_met_la_wind_letter is shadowed within the CASE WHEN expression. To work around the error, use a different alias, e.g.:

SELECT\n  wind_letter.pm_station,\n  wind_letter.RH,\n  (CASE WHEN wind_letter.wind_direction="W" THEN (\n       SELECT ss.W FROM \n       `Airnow_dataset.station_neighbour` ss \n       WHERE ss.pm_station=wind_letter.pm_station ) \n   ELSE "na" END) as neighbour_wind_direction\nFROM `Airnow_dataset.adjustedTime_met_la_wind_letter` wind_letter\n

Bozhack-miller

posted on