Oracle SQL invalid number format exception

Question

I am getting an exception while executing the following query:

String queryString="select CONCAT( CONCAT(invoicingCollection.settleNo, '- '),((invoicingCollection.netAmountTcy - invoicingCollection.setlAmountTcy)+( invoicingCollection.taxAmountTcy-invoicingCollection.setlTax‌AmountTcy))) from InvoicingCollection as " + "invoicingCollection where invoicingCollection.portfolio in (:portfolio)";

Query query = _em.createQuery(queryString);
query.setParameter("portfolio",portfolio);
List<String> querylist=query.getResultList();

The error I am getting is:

ORA-01722: ORA-01722 invalid number

Does anybody know a possible fix for this error?

P.S. When I execute the same in SQL Developer am getting the result I want.

Answer 1

ORA-01722 is Invalid number error. You've attempted to either explicitly or implicitly tried to convert a character string to a number and it is failing.

There is also a problem with your query. Oracle supports table aliases AS in the SELECT list but not in the FROM list.

Try this updated query:

String queryString="SELECT Concat(Concat(invoicingcollection.settleno, '- '), ( (invoicingcollection.netamounttcy - invoicingcollection.setlamounttcy ) + ( invoicingcollection.taxamounttcy - invoicingcollection.setltax‌amounttcy ) )) FROM   invoicingcollection invoicingCollection WHERE  invoicingcollection.portfolio IN ( :portfolio ) ";