Home » Bigquery

Bookmark · Report · More actions.. Lock comments · Pin thread

Google BigQuery error: No matching signature for operator >=

Error Message: No matching signature for operator >= for argument types: STRING, INT64. Supported signatures: ANY >= ANY at [1:60]

while running the below R script to fetch some data from BiqQuery

\n\n

This is where I get the error:

\n\n

a <- dbGetQuery(db,\npaste0("select * from dta.tbl where col1='",\nsomevariable"' and date>=",substr(gsub("\\D","",as.character(start.date)),3,8),\n" and date<=",substr(gsub("\\D","",as.character(end.date)),3,8)))\n

\n\n

Solution:

The data values are missing the single quote enclosure. eg date >='2019-01-01', it should work after adding the single quotes

\n\n

a <- dbGetQuery(db,\npaste0("select * from dta.tbl where col1='",\nsomevariable,"' and date>='",substr(gsub("\\D","",as.character(start.date)),3,8),\n"' and date<='",substr(gsub("\\D","",as.character(end.date)),3,8), "'"))\n

David-Spring

posted on

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Post Comment

SELECT SUBSTR(CAST(CURRENT_DATE AS DATE) + (CAST(d1.digit*10 +d2.digit AS INT64)-11)) AS Calendar_Date

, SUBSTR(CAST(CURRENT_DATE AS DATE) + (CAST(d1.digit*10 +d2.digit AS INT64)-11)) AS Financial_Date

FROM

( SELECT '1' as digit1

UNION ALL SELECT '2'

UNION ALL SELECT '3'

UNION ALL SELECT '4'

UNION ALL SELECT '5'

UNION ALL SELECT '6'

UNION ALL SELECT '7'

UNION ALL SELECT '8'

UNION ALL SELECT '9'

UNION ALL SELECT '0'

) d1

CROSS JOIN

( SELECT '1' as digit2

UNION ALL SELECT '2'

UNION ALL SELECT '3'

UNION ALL SELECT '4'

UNION ALL SELECT '5'

UNION ALL SELECT '6'

UNION ALL SELECT '7'

UNION ALL SELECT '8'

UNION ALL SELECT '9'

UNION ALL SELECT '0'

) d2

WHERE CAST(d1.digit1*10 +d2.digit2 AS INT64) <=20

)

For above query i'm getting this error

Can you please help me out

No matching signature for operator * for argument types: STRING, INT64. Supported signatures: INT64 * INT64; FLOAT64 * FLOAT64; NUMERIC * NUMERIC

ReplyMore..ReportDelete

You get this error when you perform a multiplication between an integer column and string. The solution is to do an explicit casting. convert the below line from :

WHERE CAST(d1.digit1*10 +d2.digit2 AS INT64) <=20

to

WHERE ((CAST(d1.digit1 AS INT64)*10) + CAST(d2.digit2 AS INT64)) <=20

ReplyMore..ReportDelete