Reginald Ho Reginald Ho - 1 month ago 14
SQL Question

Oracle error:missing right parenthesis

This is my code:

SELECT SIGHTING_ID,MAX((SQRT(POWER(LATITUDE-(-28),2)+(POWER(LONGITUDE-151,2)) AS DISTANCE
FROM SIGHTINGS


I think the problem is the bracket. I have tried lots of combination but the error still here. What can I do or the query is wrong?

Answer

As mentioned in error you missing Right Parenthesis. Here is the correct query

SELECT SIGHTING_ID,
       Max(Sqrt(Power(LATITUDE - ( -28 ), 2) + ( Power(LONGITUDE - 151, 2) ))) AS DISTANCE
FROM   SIGHTINGS                                                          --^^
GROUP BY SIGHTING_ID         

Added two right parenthesis and removed one unwanted left parenthesis. The above query returns the maximum distance for each SIGHTING_ID

If you only the record with maximum distance then try this

SELECT *
FROM   (SELECT SIGHTING_ID,
               Sqrt(Power(LATITUDE - ( -28 ), 2) + ( Power(LONGITUDE - 151, 2) )) AS DISTANCE
        FROM   SIGHTINGS
        ORDER  BY DISTANCE DESC) 
WHERE  ROWNUM = 1 
Comments