Oracle / Java number format

Question

Oracle / Java number format

Navigation

#1 by (0 votes)

1

I have the following query:

SELECT distinct ln.gps_id,GPS.LATITUD,GPS.LONGITUD,
to_number(substr(REPLACE(GPS.LONGITUD,'N',''),1,2))||'.'||TO_NUMBER(substr(substr(to_number(REPLACE(GPS.LONGITUD,'N','')),3,10)/60,2,6))||',-'||
TO_NUMBER(substr(GPS.LATITUD,1,3))||'.'||TO_NUMBER(substr(substr(REPLACE(GPS.LATITUD,'W',''),4,10)/60,2,10)) as data
FROM XXQP.XXQP_DIARIO_LN_TBL LN 
INNER JOIN XXQP.XXQP_GPS_TBL GPS
ON LN.GPS_ID=GPS.GPS_ID
WHERE LN.DIARIO_ID=355 
AND LN.GPS_ID IS NOT NULL;

When I execute it in Toad it throws the information correctly, however if this query I put it in java it marks me the error ORA-01722: invalid number, I do not know where to apply the conversion to number or if I need something else.

Greetings.

java oracle

asked by Jairo Ordaz Moreno 03.01.2017 в 19:25

source

1 answer

Error: java.lang.NullPointerException I want to print a random string of my Array

score 0 · Answer 1

I think the problem is in the division by 60 that you are applying to a VARCHAR2 and in which Oracle is doing an implicit conversion.

I think the correct thing would be:

TO_NUMBER(substr(to_number(substr(REPLACE(GPS.LONGITUD,'N',''),3,10))/60,2,6))

Who performs this sequence of operations:

vv = REPLACE(GPS.LONGITUD,'N',''); //varchar2
yy = substr(vv,3,10); //varchar2
xx = to_number(yy); //number
zz = xx/60; //number
ww = substr(zz,2,6); //varchar2
rr = TO_NUMBER(ww); //number