CODEWITHSUNDEEP
sqlgoogle-bigquery
WJA
WJA

Reputation: 7004

BigQuery no matching signature when trying to filter rows based on on list of integers

I am trying to extract certain rows where the column Code matches one of the integers in a list:

SELECT DISTINCT *
FROM `detail_table`
WHERE Date BETWEEN '2019-03-12' AND '2019-03-15'
AND Code IN UNNEST([36516, 152])

However, I get the error:

google.api_core.exceptions.BadRequest: 400 No matching signature for operator IN UNNEST for argument types: STRING, ARRAY

What am I supposed to do to make it work? When its strings then it works fine, but I have to filter on integers.

Upvotes: 2

Views: 613

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Use below instead 

#standardSQL
SELECT DISTINCT *
FROM `project.dataset.detail_table`
WHERE `date` BETWEEN '2019-03-12' AND '2019-03-15'
AND Code IN UNNEST(['36516', '152'])

OR 

#standardSQL
SELECT DISTINCT *
FROM `project.dataset.detail_table`
WHERE `date` BETWEEN '2019-03-12' AND '2019-03-15'
AND CAST(Code AS INT64) IN UNNEST([36516, 152])

OR even just (depends on your case) 

#standardSQL
SELECT DISTINCT *
FROM `project.dataset.detail_table`
WHERE `date` BETWEEN '2019-03-12' AND '2019-03-15'
AND Code IN ('36516', '152')

Upvotes: 6

Related Questions