| using a cursor [message #578516] |
Thu, 28 February 2013 22:09  |
|
|
hi
I work in oracle 10g with devsuite forms and reports 9i.
I have a problem in sql query.
There are 3 tables
1) APPLICATION
2) Appln_Authority
3) Appln_Nominee
Every application will have a authority of payment
Application table primary key is appln_pk . This is the foreign key for authority and nominee table
My problem is there are more 2 nominees and recovery amount has to be made to 1 nominee out of the calculated pension amount.
In this case there will be 2 authority numbers and in one authority number, recovery amount should be exhibited and in other it should not.
My report runs with application number and authority number as parameter. All cases will not have recovery to be made. I created a function
function CF_ADN_AMT return NUMBER is
V_AMT NUMBER(14);
BEGIN
SELECT SUM(ADN_RCVY_AMT)
INTO V_AMT
FROM APPLICATION A, T_APPLN_NOMINEE B
WHERE A.APPLN_PK=B.ADN_APPLN_PK
AND A.APPLN_NO=:PRM_APPLN_NO;
RETURN(V_AMT);
EXCEPTION WHEN OTHERS
THEN RETURN NULL;
END;
I have created a field in report paper layout and call this CF_ADN_AMT.
when I run the report the recovery amount gets exhibited for both the nominees. It should be only for the nominee where the recovery is made.
Appln_pk is the primary key for application table
Apa_pk is the primary key for authotity table
Adn_pk is the primary key for nominee table.
I would like the function created to be modified so that it picks the ADN_PK which has the recovery amount and linked to the authority number. Should cursor be used .
I would like to get help in suggesting improvement in the query to get the desired result.
[EDITED by LF: fixed [code] tags]
[Updated on: Fri, 01 March 2013 00:09] by Moderator Report message to a moderator |
|
|
|
| Re: using a cursor [message #578521 is a reply to message #578516] |
Fri, 01 March 2013 00:08  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Quote:the recovery amount gets exhibited for both the nominees. It should be only for the nominee where the recovery is made.
If function returns correct result, then it might be an easy task to "hide" the value for nominees without recovery. In order to do that, you'd create a Format Trigger for that page layout item:(if "recovery" is a number; you might play with it to adjust it, such asreturn :recovery is not null;
If that's not what you are looking for, please, provide a test case (CREATE TABLE and INSERT INTO several sample records which clearly describe what you are saying, as well as desired output based on that input) so that we could test code YOU wrote above, as well as write our own code.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
