Why are you looping over all your rows, only to actually do something when a certain condition is true? Add that condition to your where clause.
Why do this inside a function? Why not straight sql?
You use nvl twice. One of those is not needed.
Just a few of my observations. But mostly: Re: 2. How do I ask a question on the forums?
I'd say something like below.
CREATE OR REPLACE FUNCTION APPS.get_disposal_value (p_asset_id IN Number,
p_period IN VARCHAR2)
-- p_transaction_code IN VARCHAR2,
-- p_transaction_type IN VARCHAR2)
with rec_get_code as (
select transaction_type_code from fa_transaction_headers where asset_id=p_asset_id
select nvl(current_cost,0) into lc_cost_addition
where rec_get_code.transaction_type_code ='FULL RETIREMENT'
(select max(transaction_header_id) from fa_transaction_headers where transaction_header_id
NOT IN(select max(transaction_header_id) from fa_transaction_headers where asset_id=p_asset_id)
and asset_id=p_asset_id -- 907628, 914289, 924101
when no_data_found then lc_cost_addition:=0;
when too_many_rows then null; --- what to do when too many rows???
But what is to be the dependency between what is selected from FA_FINANCIAL_INQUIRY_COST_V and rec_get_code??? There appear to be no join. How do you handle no_data_found and how do you handle too_many_rows?
how do you handle too_many_rows?
Oh yes, I missed that one. In OP's own code he or she handles it by simply overwriting the previous result if there is more than one...
And wouldn't the nvl cover no_data_found?
no_data_found if encountered when calling the function in a SQL statement will make the function return null if that exception is not intercepted in the function body. If the function is called in some pl/sql block, then a no_data_found unhandled will cause the exception to be raised to the calling pl/sql block, where it should be handled somehow (how?)
Not sure and should not really comment without knowing the data and data objects involved in your query, but just a try is better than silently watching the thread, hence I wanted to ask you if this is what you are trying to get ?
<Saying so I haven't tested it by any means. Just replying may be it would trigger something in your thought process.>
SELECT NVL (current_cost, 0) FROM FA_FINANCIAL_INQUIRY_COST_V a, (SELECT MAX (transaction_header_id) FROM (SELECT transaction_header_id, DENSE_RANK () OVER (ORDER BY transaction_header_id DESC) rnk FROM fa_transaction_headers WHERE asset_id = p_asset_id) WHERE rnk = 2 AND TRANSACTION_TYPE_CODE = 'ADDITION' AND period_entered <= p_period) b WHERE a.TRANSACTION_HEADER_ID_IN = b.transaction_header_id AND b.transaction_type_code = 'FULL RETIREMENT';
Is there only ever going to be one row in fa_transaction_headers with transaction_type_code ='FULL RETIREMENT'?
If there is then the loop is completely pointless - just add a check on transaction_type_code to the select into and remove the loop and cursor.
If there can be more than one then you're going to be running the inner select multiple times and each time you will overwrite the results from the previous time. Which is obviously wrong (especially since the cursor doesn't have an order by, if it did then the code might just be inefficient, depending on what the logic should be).
Whatever you are trying to do can almost certainly be done in a single select statement, but we can't really tell you what that select should be since your current code doesn't actually make sense and you've not explained the requirements.
Almost you are correct but not sure whats the wrong in inner query ...
I am working on it .... Can you Please check once on the inner query.
Any how Thanks a lot to all and especially to you
You are looping over rows - however you have no "Order By" on the cursor. So you're only ever going to get the value of the last row in "lc_cost_addition", and the last row is non-determinate - it could in theory change each time you run the query!