REM Finds the 5 closest customers to warehouse_id = 3
REM who reside in NY state, and return the distance in miles, 
REM and order the results by distance

REM
REM CUSTOMERS and WAREHOUSES
REM


set lines 132
set pages 1000


select /*+ordered*/ 
       c.customer_id, 
       c.cust_address.state_province state,
       sdo_nn_distance(1) distance_in_miles
from warehouses w, 
       customers c
where w.warehouse_id = 3
and sdo_nn (c.cust_geo_location, w.wh_geo_location, 
            'sdo_batch_size =5 unit=mile', 1) = 'TRUE'
and c.cust_address.state_province = 'NY'
and rownum < 6
order by distance_in_miles;