Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

subtracting two rows from the same column

548123Apr 4 2012 — edited Apr 4 2012
Hi,
I have the following table with about 6000 rows of different year_month but I am compare and subtract v
Organization Year_month Contribution
Kano JAN-2011 200000
KADUNA JAN-2011 300000
ABUJA JAN-2011 400000
Kano FEB-2011 300000
KADUNA FEB-2011 200000
ABUJA FEB-2012 600000
I want to select a year_month at run time to subtract the contribution of the first year_month from the contribution of the second year_month and give me the result as shown in the following table
Organization JAN-2011 FEB-2011 diffrence
Kano 200000 300000 -100000
KADUNA 300000 200000 100000
ABUJA 400000 600000 -200000
Here is my code returning too many va

create or replace function "GET_MONTHLY_VALUE"
(q_name in VARCHAR2,
hmoCode in VARCHAR2)
return VARCHAR2
is
c1 number;
begin
select NHIS_CONTRIBUTION into c1 from CONTRIBUTION_MGT where upper(YEAR_MONTH)=upper(q_name) and upper(ORGANIZATION)=upper(hmoCode);

return c1;
exception when NO_DATA_FOUND THEN
return null;
end;

------a call to the above function:

create or replace function process_cont_monthly return varchar

is
Cursor cont_cursor is select ORGANIZATION from CONTRIBUTION_MGT;
begin
for
cont_rec in cont_cursor loop
select GET_MONTHLY_VALUE(:p26_month,cont_rec.ORGANIZATION) first, GET_MONTHLY_VALUE(:p26_month2,cont_rec.ORGANIZATION) second,
abs(GET_MONTHLY_VALUE(:p26_month,cont_rec.ORGANIZATION)-GET_MONTHLY_VALUE(:p26_month2,cont_rec.ORGANIZATION)) Diffrence
from CONTRIBUTION_MGT;

end loop;
commit;
end;
I became totally confused and don’t know what to do next
Any help and better guide is appreciated
This post has been answered by Brian Bontrager on Apr 4 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 2 2012
Added on Apr 4 2012
2 comments
4,498 views