I am trying to call a select query from my Java Program but i am getting Invalid Numbererror. I am trying to call the query something like this code I am getting query something like this: Result I am getting the error Invalid number in the line P.PACKAGE_ID = {0}. I gone through and come to know that if i

Invalid Number Exception in SQL when trying to call select statement from Java

I am trying to call a select query from my Java Program but i am getting Invalid Numbererror. I am trying to call the query something like this

code

 String[] l_arr = new String[]{"CUSTOMERCUMULATIVELIMIT"};

    l_rs = JDBCEngine.executeQuery(MessageFormat.format(
                        " SELECT  LIMITS,  P.PACKAGE_ID  FROM MSTGLOBALLIMITSPACKAGE P  ,MSTCUSTOMERPROFILE C  WHERE P.PACKAGE_ID = {0}   AND C.ID_CUST = ?    AND C.ID_ENTITY = ?  AND C.TYPEUSER = ?  AND C.ID_ENTITY = P.ID_ENTITY  AND P.PACKAGE_ID NOT LIKE ?",l_arr );

I am getting query something like this:

Result

SELECT LIMITS, P.PACKAGE_ID
  FROM MSTGLOBALLIMITSPACKAGE P, MSTCUSTOMERPROFILE C
 WHERE P.PACKAGE_ID = CUSTOMERCUMULATIVELIMIT
   AND C.ID_CUST = '00000144'
   AND C.ID_ENTITY = 'B001'
   AND C.TYPEUSER = 'ECU'
   AND C.ID_ENTITY = P.ID_ENTITY
   AND P.PACKAGE_ID NOT LIKE '*%'

I am getting the error Invalid number in the line P.PACKAGE_ID = {0}. I gone through and come to know that if i use to_char i can avoid this error. But I am not sure how to use to_char in P.PACKAGE_ID = {0}.

Also PACKAGE_ID is of type Varchar2 and CUSTOMERCUMULATIVELIMIT is a column in MSTCUSTOMERPROFILE of type Number.

Any help or suggestion will be appreciated. Thanks.

Answer

just add to_char to your sql, change {0} to to_char({0})

l_rs = JDBCEngine.executeQuery(MessageFormat.format( " SELECT LIMITS, P.PACKAGE_ID FROM MSTGLOBALLIMITSPACKAGE P ,MSTCUSTOMERPROFILE C WHERE P.PACKAGE_ID = to_char({0}) AND C.ID_CUST = ? AND C.ID_ENTITY = ? AND C.TYPEUSER = ? AND C.ID_ENTITY = P.ID_ENTITY AND P.PACKAGE_ID NOT LIKE ?",l_arr );

Advertisement

Answer