BigQuery error: No matching signature for operator >=

Question

I am getting an error No matching signature for operator >= for argument types: STRING, INT64. Supported signatures: ANY >= ANY at [1:60] when I run this R script to fetch some data from BiqQuery.

This is where I get the error:

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

Any clue on how do I get rid of this error? I am using standardsql here

Thanks ahead! :)

Solution

from brief glance - try below

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

Comments

[user1111]

I am getting this error now Error: unexpected string constant in: "paste0("select * from dta.tbl where col1='", somevariable"' and startdate>='"" Execution halted

[user1111]

Hi, thanks for your reply, but I am getting this error now Error: unexpected string constant in: "paste0("select * from dta.tbl where col1='", somevariable"' and startdate>='"" Execution halted

[Mikhail Berlyant]

to troubleshoot your sql just print out your final string to see where you break the syntax :o)

[user1111]

I am running it in cronjob, bit difficult to troubleshoot like that :(

[Mikhail Berlyant]

does not matter. if you know your data - you should/must - you can do it ("print" result) in your mind and see what is that executed! as an option - you can get to BQ UI and use recent queries to see what exactly query you are running

[user1111]

In BQ UI it says Syntax error: Unclosed string literal at col1=' ". But it doesn't seem to be unclosed everything seems to be closed well here

[Mikhail Berlyant]

see my update - looks like you were missing one more comma - somevariable

[user1111]

Hi Mikhail, It worked now :) I missed that comma after somevariable. Thank you so much for your quick help, Really helped me!!!

[Mikhail Berlyant]

sure. whenever you get enough reputation score - come back and vote up on this answer :o)

[user1111]

Sure I will do that! :) I have one more same query: getting blind to find the same kind of error, if you could help me again, I will be obliged. b <- dbGetQuery(db, paste0("select * from db1.tbl where date>=",paste0("20",substr(gsub("\\D","",as.character(start.‌​date)),3,8)), " and date<=",paste0("20",substr(gsub("\\D","",as.character(end.da‌​te)),3,8))))- getting the same error as above due to some quotes.

[Mikhail Berlyant]

exactly same error for exactly same issue - you are missing quotes around your dates!

[user1111]

Yes was fixing that. :) You have an eye of an eagle btw. Thanks again!!!! I will come back to vote up your answer when I will be eligible for it. :)

[Mikhail Berlyant]

i am surprised a little with you changing your mind with regard of accepted answer - up to you of course - just surprised :o) any specific reason?

[Elliott Brossard]

Not sure why mine was accepted either. I posted before I saw that you had already answered with the same thing.

[Mikhail Berlyant]

no problem at all :o) just a little strange as he accepted already mine and then after many back and forth and additional questions - he finally changed this. as I said not a problem at all - just was curious :o)

[user1111]

Hi @MikhailBerlyant and Elliott - sorry about messing up checking the right accepted answer. Fixed it. It wasn't intentional. :) Thanks again for your help.