sql - Oracle function result differs from the result of the select statement -

i have problem pl/sql function. try single result, depending on restrictions in statement. if no result matches result should null.

if execute select statement directly on table status_history correct result. if execute function id not part of range of veh_id. id table. result, expect no result.

here function:

 create or replace function is_inventory (         veh_id number,         status_date date) return number       cnt number;     begin       select sh.id   cnt   status_history sh      sh.veh_id = veh_id        , sh.code >= 100        , sh.code < 200        , sh.id =               (select id                  (  select sh2.id, sh2.status_date, sh2.code                            status_history sh2                               sh2.veh_id = veh_id                                 , sh2.status_date <= status_date                        order sh2.status_date desc, sh2.code desc)                 rownum = 1);      if cnt > 0       return cnt;     else       return cnt;     end if;     end is_inventory;     / 

maybe function written better, think should same result if execute function or sql statement directly.

in generell result of function should not return id. first tried count, debuggin changed result id.

thank help.

the following where clause not expect:

where sh2.veh_id = veh_id , sh2.status_date <= status_date  

it is doing:

where sh2.veh_id = sh2.veh_id , sh2.status_date <= sh2.status_date  

it not plugging in variables. because of scoping rules of sql, first looks matching column names before looking variable names. right fix prefix parameters , variables distinguishes them column names.

create or replace function is_inventory (     v_veh_id number,     v_status_date date) return number     v_cnt number; begin . . .