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:

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

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
  wind_letter.pm_station,
  wind_letter.RH,
  (CASE WHEN wind_letter.wind_direction="W" THEN (
       SELECT ss.W FROM 
       `Airnow_dataset.station_neighbour` ss 
       WHERE ss.pm_station=wind_letter.pm_station ) 
   ELSE "na" END) as neighbour_wind_direction
FROM `Airnow_dataset.adjustedTime_met_la_wind_letter` wind_letter

Bozhack-miller

posted on 23 Nov 18