Originally posted by dba_admin
Question: Does Query2 call the DoCalculate twice for every row processed? (One in the select and one in where clause.)
No, not for every row processed. But, yes, for every row returned.

For the rows where function DoCalculate() result in NULL, the function is executed only once (only in WHERE clause). Since in this case the result of the WHERE clause is "FALSE", Oracle doesn't bother to process that row further, so the DoCalculate() in selct column list is not executed. But for rows where DoCalculate() result in NOT NULL, function is executed twice - first time for the WHERE clause and the second time for column list.

If yes, how do I reduce the number of call to the function to 1?
I'm not sure if it can be done, at least not with pure SQL. First I thought that the following will help:
Code:
select mileage_calcualted from
(select
   DoCalculate(aLongitude,aLATITUDE,a.LONGITUDE,a.LATITUDE) mileage_calcualted
 from TABLEx a
)
where mileage_calcualted is not NULL;
however it turned out that it behaves exactly as the original query.