Where in my BigQuery sql code do I fix the syntax error?

Google Cloud Data Analytics
1

–I had no idea so many students are stuck in BigQuery, not a good sign for Google. I’ve wasted two days trying to Google a solution to my syntax error questions. The error appeared after I used the suggested sql code in the lesson. Any helpful suggestions are appreciated.

–Syntax error: Expected end of input but got identifier “dataset” at [8:16]

SELECT

stn,

date,

–Use the IF function to replace 9999.9 values,

which the dataset description explains is the default

value when temperature is missing, with NULLS instead.

IF(

temp=“9099.9”,

NULL,

temp) AS temperature,

–Use the IF function to replace 999.99 values, which

the dataset description explains is the default value

when wind speed is missing, with NULLS instead.

IF(

wdsp=“999.9”

NULL,

CAST(wdsp AS Float64)) AS wind_speed,

–Use the IF function to replace 99.99 values, which

the dataset description explains is the default value

when precipitation is missing, with NULLS instead.

IF(

prcp=“99.99”,

0,

prcp) AS precipitation

FROM

bigquery-public-data.noaa_gsod.gsod2020

WHERE

stn=“725030” --La Guardia

OR stn=“744860” --JFK

ORDER BY

date DESC,

stn ASC

1 Like
2

Somehow the copying from website to editor split all the single-line comments to multiple lines.

The error is part of one of these comments, though that line doesn’t have a comment marker so the parser thinks it’s code and tries to make sense of it. Specifically the word ‘dataset’ at line 8 character 16.

I think this query should do what you’re looking for, comparing it with yours should help find the syntax errors. (edit: added the comments back in for completeness)

SELECT
stn,
date,

 --Use the IF function to replace 9999.9 values,
 --which the dataset description explains is the default
 --value when temperature is missing, with NULLS instead.
    IF(
        temp=9999.9,
        NULL,
        temp) AS temperature,

 --Use the IF function to replace 999.99 values, which
 --the dataset description explains is the default value
 --when wind speed is missing, with NULLS instead.
     IF(
         wdsp="999.9",
         NULL,
         CAST(wdsp AS Float64)) AS wind_speed,
 
 --Use the IF function to replace 99.99 values, which
 --the dataset description explains is the default value
 --when precipitation is missing, with NULLS instead.
     IF(
         prcp=99.99,
         0,
        prcp) AS precipitation
 
 FROM
   `bigquery-public-data.noaa_gsod.gsod2020`
 WHERE
   stn="725030" --La Guardia
   OR stn="744860" --JFK
 ORDER BY
   date DESC,
   stn ASC
5 Likes
3

I copied and pasted exactly as you gave above, with no changes, and I have no errors. I also ran it to make sure, and it worked.

Try copying the above and pasting with no formatting (CTRL-SHIFT-V) into a new query (don’t paste over the existing one in case there’s some bizarre whitespace or formatting issues).

1 Like
4

This Query actually worked!!!

1 Like