Function behaves differently when put in the where clause appose to as column heading

I have a pl/sql function which resides on the database server which is called from within a select statement in an application on the client machine.

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

For each row in the scatter table the fields for the latitude and longitude values are input to the function latlong which compares them to reference latitude longitude points. If the current row is the first row in the table the reference points are assigned to the current values and 1 is returned. For the second ++ rows in the table, if either of the compared latitude / longitude points are >0.005 from the ref points then a 1 is returned and the row is included in the output from the select statement. Another if statement is used to check if the user has begun a new select statement so that the reference points are reset.

The function is within a package so that global variables can be used to remember the reference points since the function is executed for each row in the table.

The current code is…

create or replace package globalLL is
refLat number(32,10);
refLong number(32,10);
refID number(32,10);
function latLong(latitude in number, longitude in number, ID in number) return number;
end;
/

create or replace package body globalLL as
function latLong(latitude in number, longitude in number, ID in number) return number is
compLat number(32,10);
compLong number(32,10);
begin
if refLat is null and refLong is null and refID is null then
-- set default value of refLat and refLong and refID
refLat := latitude;
refLong := longitude;
refID := ID;
return 1;
end if;
if ID < refID then
-- checks if user has chosen a new table or reselected the same table and resets global variables
refLat := latitude;
refLong := longitude;
refID := ID;
return 1;
end if;
refID := ID;
compLat := abs(refLat – latitude);
compLong := abs(refLong – longitude);
if compLat > 0.005 or compLong > 0.005 then
refLat := latitude;
refLong := longitude;
return 1;
else
return 0;
end if;
end;


Now for the problem, if I do this select statement from SQL plus or the application

1)
select seq, latitude, longitude, globalLL.latLong (latitude, longitude, seq)
from scatter;

I get a result containing all of the rows in the scatter table and an indication of which would be not included (0) and which would be included (1) if I ran the following select statement.

2)
select latitude, longitude, seq
from scatter where globalLL.latLong (latitude, longitude, seq)=1

I counted the number of rows with 1s in the globalLL.latLong column for the result from sql statement 1 manually and there were 45.

When I run the 2nd sql statement it does not agree with my manual count. It returns a result containing 86 rows. This is incorrect because it should be 45!

From analysis of the two data sets in my application, when the function runs in the 2nd select statement it is appears as though the number of rows (that the function returns 0 to) between a row that returns 1 and another row that returns 1 is important. It seems when this is large the function resets itself and outputs a 1 when it really should output a 0.

Eg if works ok if row 200 returns 1 and then there are 100 rows that return 0 before the 301st row returns 1. This seems to work ok. But if row 500 returns 1 and then there are 500 rows that return 0 before the 1001st row returns 1, then extra 1s are returned in the middle when they should be 0 and hence increasing my count from 45 to 86. The lat / long distance between row 200 and 301 is the same as row 500 to 1000. There is just more points data points in the second distance.

I do not think it is a problem with the function itself but some options on oracle. Does it limit how many times a function can be run within a package. It is strange that this problem only occurs when the function is put within the ‘where’ clause of the select statement (sql 2) appose to as a column (sql 1).

Any ideas would be much appreciated.

Howard

Ok I think I have found the problem. I have two tables containing the same data. Scatter 3 is not ordered by sequence number. Scatter 4 is already ordered by sequence number.

The sql query needs to order the table by seq before applying the function to it. Since the sequence number represents the time order that the samples for latitude and longitude were collected it is important that the function processes the data in sequence order.

select latitude, longitude, seq
from (select * from scatter3 order by seq)
where globalLL.latLong (latitude, longitude, seq)=1

returns 86 == should be 45

The sql above seems to apply the function to the unordered list and then orders the results at the end of the query and not in the nested query. This is what produces the extra results because it is comparing the row at sequence number 100 with row 150 instead of 101.


select latitude, longitude, seq
from (select * from scatter4 order by seq)
where globalLL.latLong (latitude, longitude, seq)=1

returns 45 == verified by hand

The sql above returns the correct amount of results. I guess the nested order by seq is being ignored as in the sql for scatter 3. Even though scatter 4 is already ordered the nested part is included to show the ambiguity in my sql statement for scatter 3.
The tables scatter 3 and 4 are a sample from my original scatter table that contains a lot more data and additional columns which prevents me from permanently ordering it by seq.

Therefore how do I make oracle order the scatter3 table before running the function in the where clause?