New answers tagged functions
Okay, few notions about your procedure DECIMAL(12,12) means 12 digits, where ALL 12 are behind a floating point (so basically, 0.*). To see an example, execute SELECT CAST(23.45 AS DECIMAL(12,12)); and see what output does it give you. I suggest that you change DECIMAL definitions to DECIMAL(9,6) which means 9 digits, where 6 are after the point. I think ...
Here's a really simple way to get the distance between two lat/lng pairs. It's not exact, but it's pretty close. Replace lat1, lng1, lat2 and lng2 with your columns. SELECT 1609.34 * 3959 * ACOS( COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS( RADIANS(lng2) - RADIANS(lng1) ) + SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)) )
You should return something when nothing is found. Before exception comment in the statement. The way your routine is written it may does find nothing and then hits the end of the function without returning anything, hence the error function returned without value. Workaround : Handle it with the exception exception when TOO_MANY_ROWS then return ...
Top 50 recent answers are included