java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis Error

Question

I'd like to get the last available balance from a table when godownname and itemname are matched.

I tried some code. but i have a problem with last_value() function in Oracle.

I got the error:

SEVERE: null java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)

I don't know where is the Error?. Can anyone help me?

Here is my code:

String updateQuery = "select last_value(availablebalance) ignore nulls over(order by receiveddate or transdate) from t_stocks_status where godownname = ? and itemname = ?";
ps = con.prepareStatement(updateQuery);
ps.setString(1, fromField);
ps.setString(2, itemName);
rs = ps.executeQuery();
if (rs.next()) {
availableQty = rs.getInt(1);
}

Can you explain what you're trying to query? the over clause doesn't make any sense to me. — Mureinik
– Mureinik, Commented Jun 17, 2015 at 7:02
order by receiveddate or transdate What are you trying to do with it? It is syntactically incorrect. Post some sample data, explain the rules and show your desired output. — Lalit Kumar B
– Lalit Kumar B, Commented Jun 17, 2015 at 7:04
This is a transaction Table, here I want to get the last updated or inserted availablebalence field value from the table when godownname and itemnames are matched. ( consider that there are more transactions are done. In this environment get only the last available balance field value). — Thirunavukkarasu
– Thirunavukkarasu, Commented Jun 17, 2015 at 7:17

Ashish Patil · Accepted Answer · 2015-06-17 07:25:47Z

0

Your order by clause is having problem.over(order by receiveddate or transdate)

it may be like over(order by receiveddate,transdate)

Take a look at Order by clause syntax from oracle docs--

ORDER BY { column-Name | ColumnPosition | Expression } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [ , column-Name | ColumnPosition | Expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ] *

answered Jun 17, 2015 at 7:25

Ashish Patil

4,6421 gold badge17 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related