I have a java function which is called from within a select statement.

select latitude, longitude from scatter where latlong(latitude, longitude)=1;

For each latitude and longitude field in the scatter table the values are input to the function latlong which compares them to reference latitude longitude points. If the compared points are >2 from the ref points then a 1 is returned and the row is included in the output from the select statement.

The current code is…

import java.io.*;
import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class latLong
{
public static String compLatLong (String latitudeS, String longitudeS) throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection:");

// these need to be set from the obtained sql variables
// some how need to load them in
int reflat=0;
int reflong=0;

// assuming values have been loaded in

// check to see if the variables do not have a value
if ((reflat==0)||(reflong==0))
{
// must be first row in scatter table
// set values of
reflat=Integer.parseInt(latitudeS.trim());
reflong=Integer.parseInt(longitudeS.trim());
// write out sql variables here
return (new String("1"));
}
else
{
// compare the lat / long points
int latitude=0;
int longitude=0;
latitude = Integer.parseInt(latitudeS.trim());
longitude = Integer.parseInt(longitudeS.trim());
int compLong=latitude-reflat;
int compLat=longitude-reflong;
compLong=Math.abs(compLong);
compLat=Math.abs(compLat);

// check the distance between the 2 points

if ((compLong<=2)&&(compLat<=2))
return (new String("0"));

else
// update sql variables here
return (new String("1"));
}
}

}

I would like to know how to modify the code so that the reference latitude and longitude points are obtained from sql (session??) variables which are updated each time the function is executed.

So when the function is first called from the select statement it checks to see if the lat and long sql variables exist. If they do not it creates them and assigns them the current values of latitude and longitude that were passed to the function from the scatter table. It will then return 1 so that the first row from the table is always included in the results table.

For the second row in the scatter table the function will load the sql variables lat and long into the java code that were defined above and compare them to the current values of latitude and longitude that were passed to the function from the scatter table. If the difference between them is <=2 then the function returns 0 and the values of lat / long need not be updated. However if the difference is >2 the function should update the sql variables with the current latitude and longitude and them return 1.

This then repeats for all rows in the scatter table producing a results table that contains latitude and longitude points that are >2 units apart.

Would a pipelined function work better? How would I create one that is suitable for my selection objective?

Thanks Howard