It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes someone please help me out with this thing Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "someone please help me out with this thing" Watch "someone please help me out with this thing" New topic

someone please help me out with this thing

Chintan Shah

Joined: Jun 29, 2005
Posts: 27
Here is the query for retrieving data from Oracle.

query="SELECT DISTINCT * FROM netview WHERE date1= '" + request.getParameter("showdate4") + "' AND onlinetotal=(SELECT MAX(onlinetotal) FROM netview where date1='"+request.getParameter("showdate4")+"') AND boxofficetotal=(SELECT MAX(boxofficetotal) FROM netview WHERE date1='"+request.getParameter("showdate4")+"')";

now..when i run same query in mysql it shows

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'select max(onlinetotal) from netview)' at line 1

can anyone let me know the syntax for MySQL..for this thing.
thanks in advance.
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
I don't see anything that wouldn't work on MySQL 5 (but I'm not a MySQL
expert). What version of MySQL are you using?

If I were doing this on Oracle, I'd right it substantially differently...

Maybe that or something like it will work on MySQL.

By the way, PreparedStatement is more secure, and generally faster on Oracle.

One more thing, you probably know this, but I find the purpose of the query somewhat baffling; it will only produce a result row when the 2 maximums happen to occur in the same row, and no result rows otherwise. Maybe I'm too tired tonight, but I can't see the practical use of that.
Chintan Shah

Joined: Jun 29, 2005
Posts: 27
The query which i wrote....retrieves the row which has maximum online total and maximum box office total.Basically it will retrieve only the last entered row with maximum of both things. I cant do it in MySQL..i am using MySQL 4.3.
Thnx for help.
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
Hi Chintan,

Please post a desc of your table with some sample data as it is quite difficult to make an assumption on a syntax error without knowing what the syntax should be.

Looking at you query I would say you are missing a GROUP BY clause with your MAX statement...I mean if you have just one record for that date there is no need for max if there is more you probably need a group by, but I may be wrong as I am lacking some info .

I would suggest using MySQL Query Browser for the online functions and statements help, if you cannot figure it out with its help, post agian with the additional info I requested.

When you are troubleshooting sql statements you should run each statement separately with sample values check the results and then combine them.

I hope this helps.
I agree. Here's the link:
subject: someone please help me out with this thing
It's not a secret anymore!