Reputation: 41
I am trying to write a function that returns a 1 if it finds the account description already in the table and a 0 if it does not.
I can get it to return the 0 fine, but I cant seem to get it to return 1
Create or replace function test_glaccounts_description
(
var_account_desc varchar2
)
return number
as var_status number
begin
select 1 into var_status
from general_ledger_accounts
where account_description = var_account_desc;
exception
when no_data_found then
var_status := 0;
return var_status;
end;
/
This is what it is supposed to do
Code a function named test_glaccounts_description that accepts one parameter that tests whether an account description is already in the General_Ledger_Accounts table. This function should return a value of 1 if the account description is in the table or zero if it isn't. (Note: If a SELECT statement doesn't return any data, it throws a NO_DATA_FOUND exception that your function can handle.)
Upvotes: 3
Views: 1110
Reputation: 206929
You need to return from all paths in your code.
Something like this should work:
begin
select 1 into var_status
from general_ledger_accounts
where account_description = var_account_desc;
return var_status;
exception
when no_data_found then
var_status := 0;
return var_status;
end;
/
Upvotes: 4