Home » Developer & Programmer » Reports & Discoverer » using a cursor (oracle 10g, devsuite forms and reports 9i)
using a cursor [message #578516] Thu, 28 February 2013 22:09 Go to next message
lacchhii
Messages: 118
Registered: May 2009
Location: bangalore
Senior Member

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 Go to previous message
Littlefoot
Messages: 19689
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:
return :recovery > 0;
(if "recovery" is a number; you might play with it to adjust it, such as
return :recovery is not null;
or similar.

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.
Previous Topic: Change Line Graph Value Through Oracle Report Parameter
Next Topic: Incorrect page number at Oracle Reports (2 threads merged by bb)
Goto Forum:
  


Current Time: Wed Oct 22 19:43:23 CDT 2014

Total time taken to generate the page: 0.09069 seconds