error in mysql query(syntax)

[ajos]

hi friends,
i have a problem in mysql query..when i click on submit the values get enterred but i get a

SQLException
SQLState:42000
Message:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') GROUP BY left(a.expenditure_code,5), expenditure_type' at line 1
Vendor:1064

ever since i did some changes in my code(earlier i was getting a null pointer exception).my query goes like this-->

String s2 = "SELECT sum(paid_amt), left(a.expenditure_code,5), expenditure_type FROM expenditure_requisition_detail AS a, Publicity_Head_Master AS b WHERE a.expenditure_code = b.expenditure_code and req_code in (SELECT Req_Code FROM Expenditure_Requisition_Master WHERE ";
            s2 = s2 + s3 + ") GROUP BY left(a.expenditure_code,5), expenditure_type";
 

can someone please tell me where is the syntax error?
thanks in advance
regards,
ajos

[amitpatel66]

hi friends,
i have a problem in mysql query..when i click on submit the values get enterred but i get a

ever since i did some changes in my code(earlier i was getting a null pointer exception).my query goes like this-->

String s2 = "SELECT sum(paid_amt), left(a.expenditure_code,5), expenditure_type FROM expenditure_requisition_detail AS a, Publicity_Head_Master AS b WHERE a.expenditure_code = b.expenditure_code and req_code in (SELECT Req_Code FROM Expenditure_Requisition_Master WHERE ";
            s2 = s2 + s3 + ") GROUP BY left(a.expenditure_code,5), expenditure_type";
 

can someone please tell me where is the syntax error?
thanks in advance
regards,
ajos

Please POST the value of s3 for better understanding.

[ajos]

Please POST the value of s3 for better understanding.

thanks for the reply amitpatel66,
well its a conditional check if i can say...ok here goes..

s3 = s3 + "Branch = '" + s5 + "' ";
        s4 = "Y";
        if(!s6.equals("0"))
        {
            if(s4 == "Y")
                s3 = s3 + " AND ";
            s3 = s3 + "cost_Center = '" + s6 + "'";
            s4 = "Y";
        }
        if(s7.equals("0"))
        {
            System.out.print("");
        } else
        {
            if(s4 == "Y")
                s3 = s3 + " AND ";
            s3 = s3 + "publicity_Code = " + s7;
            s4 = "Y";
        }
        if(s8.length() > 0)
        {
            if(s4 == "Y")
                s3 = s3 + " AND ";
            s3 = s3 + "Req_Date BETWEEN #" + s8 + "# AND #" + s9 + "#";
            s4 = "Y";
        }
        String s = "";
        String s1 = "";
 

hope u got it
regards,
ajos

[Atli]

Hi.

It looks to me like you are trying to add two strings using the "+" operator.

In PHP you should use a period "." when adding two strings:

$str = "First " . "Second";
 
$str2 = $str . " Third";
 
$str2 .= " Fourth";
 
echo $str2;
# Prints: First Second Third Fourht
 

Edit:
Looking closer at your code, I see you are not using PHP... The way you did it works in Java, as far as I know... So just ignore this post :)

[r035198x]

and in Java always compare strings using the .equals method.
== compares references.

[ajos]

and in Java always compare strings using the .equals method.
== compares references.

thanks guys for all the replies.
hey r035198x,
i previously was comparing the strings using the .equals method, but it was giving me null pointer exceptions,so the change but ever since this change i am getting this mysql state 42000 exception...what should i do to avoid this exception and null pointer exception also.thanks,
ok guys i have eliminated the null pointer exception by making some changes here--->

String s6 = httpservletrequest.getParameter("cost_Center");
String s7 = httpservletrequest.getParameter("publicity_Code");
 

here i have made the 'C' capital letter.but the sql exception stays.please help me with my sql query.thanks
regards,
ajos

[r035198x]

Please post two things:
1.) The full exception trace that was thrown
2.) The full mysql query that you had passed to the MySQL engine. (You can get this by doing a println of the String before executing the sql)

[ajos]

friends, problem solved!!!! hurray(this joy is temporary though!)
actually i had something like this in my jsp page-->
[HTML]
<html:option value="0"></html:option>
<html:option value="41601">Outdoor</html:option>
......and so on
[/HTML]

and in my class

if(!s6.equals("0"))
        {
            if(s4 == "Y")
                s3 = s3 + " AND ";
            s3 = s3 + "cost_center = '" + s6 + "'";
            s4 = "Y";
            System.out.println("enterring s6");
        }
and so on....
 

i forgot to put the 0 in my <html:option> and so the compiler wasnt getting the value 0 and hence the exception! phew....i simple word can cause such problems...i should be getting a kick for this in my back side..haha:)
regards,
ajos