error in your SQL syntax: when passing nested query in executeQuery() method

Question

Sorry for such a silly question.

below is the actual query:

SELECT  DATE_FORMAT(b.maxtime, "%H:%i") as ts, b.max_value-b.min_value as 
diff FROM (SELECT max(Anzahl) as max_value ,min(Anzahl) as min_value,  
greatest(max(ts),min(ts)) as maxtime  
FROM customer where ts rlike '^2017-06-06' and Typ="B4A"
GROUP BY hour(ts)) b;

And when I am passing it in executeQuery() method using java like below:

resultset =statement.executeQuery("SELECT DATE_FORMAT(b.maxtime,\"%H:%i\") 
as ts, b.max_value-b.min_value as diff(SELECT max(Anzahl) as max_value 
,min(Anzahl) as min_value, max(ts) as maxtime from customer where Typ = " + 
"\"" + selected_value+"\""+" "+"and ts RLIKE"+"'^"+cal_value+"'"+ "GROUP BY 
hour(ts))b");

I am getting error in SQL syntax. and Error is:

MySQL server version for the right syntax to use near '(SELECT max(Anzahl) as max_value ,min(Anzahl) as min_value, max(ts) as maxtime f' at line 1

Can someone help me out to figure out the problem.

You have some missing spaces in there, which is probably what is causing the error. But you should learn about prepared statements, which would help to avoid this problem. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 6, 2018 at 9:48
@DawoodibnKareem I think MySQL will accept either single or double quotes. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 6, 2018 at 9:48
It shouldn't. Double quotes have a different meaning entirely. — Dawood ibn Kareem
– Dawood ibn Kareem, Commented Jun 6, 2018 at 9:49
@DawoodibnKareem they have no difference in my local mysql database(5.0),I think it might has something to do with the version — flyingfox
– flyingfox, Commented Jun 6, 2018 at 9:54

Roshana Pitigala · Accepted Answer · 2018-06-06 09:49:51Z

1

It's a typo. In the actual query there's a FROM after ...b.min_value as diff. You have left it out in the String which you are parsing to executeQuery()

...diff FROM (SELECT max(Anzahl)...

So the complete statement would be

resultset = statement.executeQuery("SELECT DATE_FORMAT(b.maxtime,\"%H:%i\") 
as ts, b.max_value-b.min_value as diff FROM (SELECT max(Anzahl) as max_value 
,min(Anzahl) as min_value, max(ts) as maxtime from customer where Typ = " + 
"\"" + selected_value+"\""+" "+"and ts RLIKE"+"'^"+cal_value+"'"+ "GROUP BY 
hour(ts))b");

answered Jun 6, 2018 at 9:49

Roshana Pitigala

8,8569 gold badges53 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Roshana Pitigala Over a year ago

My pleasure... :)

Collectives™ on Stack Overflow

error in your SQL syntax: when passing nested query in executeQuery() method

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related