Call function in select statement

[kwil38]

I'm trying to use a function in the select statement for a view to return a value which will be a column in the view. The function compute and returns a value when I run it alone, but inside the Select statement it causes my view to not return any rows. Not sure what I'm doing wrong. Any ideas would be greatly appreciated!

Here's my function:

CREATE OR REPLACE FUNCTION GET_CAR_GL_ACCOUNT
(i_gl_reference IN circ.car_gl_post_acct.code%TYPE,
i_publication IN circ.car_gl_post_acct.from_pub%TYPE,
i_adi IN circ.car_gl_post_acct.from_adi%TYPE)
RETURN NUMBER
IS
o_account circ.car_gl_post_acct.debit%TYPE;
BEGIN
begin
SELECT debit
INTO o_account
FROM circ.car_gl_post_acct
WHERE i_gl_reference = car_gl_post_acct.code
AND i_publication >= car_gl_post_acct.from_pub
AND i_publication <= car_gl_post_acct.to_pub;
dbms_output.put_line(o_account);
GOTO return_account;
exception
WHEN TOO_MANY_ROWS THEN
GOTO lookup_with_adi;
WHEN NO_DATA_FOUND THEN
GOTO lookup_with_adi;
end;
<<lookup_with_adi>>
begin
SELECT debit
INTO o_account
FROM circ.car_gl_post_acct
WHERE i_gl_reference = car_gl_post_acct.code
AND i_publication >= car_gl_post_acct.from_pub
AND i_publication <= car_gl_post_acct.to_pub
AND i_adi >= car_gl_post_acct.from_adi
AND i_adi <= car_gl_post_acct.to_adi;
dbms_output.put_line(o_account);
GOTO return_account;
exception
WHEN NO_DATA_FOUND THEN
o_account := '';
GOTO return_account;
end;
<<return_account>>
RETURN o_account;
END GET_CAR_GL_ACCOUNT;



Here's my View:
CREATE OR REPLACE VIEW VW_WEHCO_GL_CAR_TRANS_RETURNS2
(AMOUNT, APPLIED_DATE, PUBLICATION, ROUTE, GL_REFERENCE,
DISTRICT, ADI, DESCRIPTION, DEBIT, CREDIT,
"o_account")
AS
SELECT carrier_trans.amount,
carrier_trans.applied_date,
carrier_trans_returns.publication,
carrier_trans_returns.route,
carrier_trans_code.gl_reference,
route.district,
route.adi,
car_gl_reference.description,
car_gl_post_acct.debit,
car_gl_post_acct.credit,
wehco.get_car_gl_account(carrier_trans_code.gl_reference,carrier_trans_returns.publication,route.adi) "o_account"
FROM circ.carrier_trans_code,
circ.carrier_trans_returns,
circ.carrier_trans,
circ.route,
circ.car_gl_reference,
circ.car_gl_post_acct
WHERE carrier_trans.trans_num = carrier_trans_returns.trans_num
AND carrier_trans_code.code = carrier_trans.code
AND carrier_trans.trans_type = 'RE'
AND carrier_trans_returns.publication = route.publication
AND carrier_trans_returns.route = route.route
AND security.pk_security.chk_row_route( route.publication, route.division, route.adi, route.district, route.depot, route.route, 'S') = 'Y'
AND carrier_trans_code.gl_reference = car_gl_reference.code
AND car_gl_post_acct.code = carrier_trans_code.gl_reference

 

[SantaMufasa]

...inside the Select statement it causes my view to not return any rows.

I disagree, Kwil, if the function was causing your view to not return any rows, then it would need to appear in your WHERE clause. You can test my assertion by temporarity running the following code:

SELECT carrier_trans.amount,
       carrier_trans.applied_date,
       carrier_trans_returns.publication,
       carrier_trans_returns.route,
       carrier_trans_code.gl_reference,
       route.district,
       route.adi,
       car_gl_reference.description,
       car_gl_post_acct.debit,
       car_gl_post_acct.credit,
/*
       wehco.get_car_gl_account(carrier_trans_code.gl_reference,carrier_trans_returns.publication,route.adi) */ 999 "o_account"
FROM...

You will still get "no rows selected" (if you were getting "no rows selected" previously). The WHERE clause is the only syntax that can affect the number of returning rows.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[kwil38]

Right again Santa! Turns out it was a permission problem - The user I was trying to run it as didn't have access to a needed table. Thanks again!