CODEWITHSUNDEEP
google-bigquery
Mitch VanDuyn
Mitch VanDuyn

Reputation: 2878

BiqQuery string compare gives Query Failed Error: Unexpected. Please try again

I have a simple query like this:

SELECT IF(production_center='production center 1',total_sales_less_taxes,0)
     as PC1_sales FROM ...

This fails with Query Failed Error: Unexpected.

depending on the value of the string 'production center 1'

for example if I change that string to 'NYC' which is also in the data base as a production_center value, it fails. If I change 'production center 1' to 'production center' it fails, but if I change it to 'production center xyzzy' its okay.

There is no pattern whatsoever to the failure mode.

The work around I am using is to replace production_center = ... with production_center CONTAINS '....'

which works fine.

Upvotes: 2

Views: 337

Answers (1)

jacek2v
jacek2v

Reputation: 590

  1. If you use LIMIT then maybe this is source of problem - insert it at the end
  2. Try other order statements.
  3. Check what is calculated when condition (production_center='production center 1') is true. Maybe the problem is with total_sales_less_taxes.

Here is working example (public shakespeare database):

SELECT IF(corpus='comedyoferrors',corpus_date,0), corpus 
FROM [publicdata:samples.shakespeare] 
WHERE corpus IN('comedyoferrors', '1kinghenryiv')

OR This ERROR in Big Query. I figure out that if I replace corpus_date by word_count then it fails. But if I use earlier field in condition (replace corpus by word) then is ok:

SELECT word, IF(word='ACT',word_count,0), corpus 
FROM [publicdata:samples.shakespeare] 
WHERE corpus IN('comedyoferrors', '1kinghenryiv')

So, the order of the fields matters.

Upvotes: 1

Related Questions