Exception occurs while executing the select statement having the where clause, using the jobs.query API in bigquery.

[shrgupta28]

Hi Team,

Can you help me with this? I have a below table created in bigquery named AllDataTypes, having an id (NUMERIC), name(STRING) and JoinDate(DATE) column

Using JAVA API jobs.query, When I try to select the data form this table using the date field in where clause, I get the below error. Exception in thread "main" com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request { "code" : 400, "errors" : [ { "domain" : "global", "location" : "q", "locationType" : "parameter", "message" : "Argument type mismatch in function EQUAL: 'JoinDate' is type int32, '2012-05-23' is type string", "reason" : "invalidQuery" } ], "message" : "Argument type mismatch in function EQUAL: 'JoinDate' is type int32, '2012-05-23' is type string", "status" : "INVALID_ARGUMENT" }

Can you help me how to execute this using java.

[shrgupta28]

Hi All, @pmakani @danoscarmike Request your team's help on this.

PFA the code snippet. sample.txt

I get the error while executing the below SQL statement using JAVA: String query = "Select * from datasetid.AllDataTypes where JoinDate='2012-05-23')"; I request your help with this.

This is happening with many datatypes like int,byte,time datetime, I need to know what is required to be done.

Thanks in advance!

[suraj-qlogic]

@shrgupta28 ,Thanks for filling issue.

Can you please try below query.

String query = "Select * from datasetid.AllDataTypes WHERE DATE(TIMESTAMP(JoinDate)) = date('2012-05-23')";

[shrgupta28]

@suraj-qlogic Yes, this works.

But what is the logic behind this, can you explain? And how do I work with the other datatypes like int,byte,time datetime?

[shrgupta28]

@suraj-qlogic Do we have any link where such details are shared by google about the SQL grammar that we use. Could you please help me with it? Thanks in advance!

[suraj-qlogic]

@shrgupta28 For Query grammar please find following link.

BigQuery Query Grammer: query-grammer