23 November 2018 · Bozhack-miller ·       Add to Favorites   Report

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 November 2018

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied