procedure to calculate two columns in a row

Question

0.00/5 (No votes)

See more:

I have table t1

ID    NAME        AGE GENDER   BALANCE
----- ----- --------- ----- ---------
1001  John         10 M            10
1002  Meena         5 F             0
1003  Nikh         11 M             0
1004  divs          7 F             0
1005  neha          4 F             0

from second row, if Gender is M then Balance (2nd row) should be age(2)+balance(1)

else Balance(1)-age(2)

Final structure should be like

ID    NAME        AGE GENDER   BALANCE
----- ----- --------- ----- ---------
1001  John         10 M            10
1002  Meena         5 F             5
1003  Nikh         11 M             16
1004  divs          7 F             9
1005  neha          4 F             5

Please help me with procedure to calculate the balance

[edit]Code block added - OriginalGriff[/edit]

Posted 7-Aug-15 23:11pm

Divya Malpani

Updated 8-Aug-15 0:04am

OriginalGriff

v2

Add a Solution

Comments

OriginalGriff 8-Aug-15 6:05am

What have you tried?
Where are you stuck?
What help do you need?

Divya Malpani 8-Aug-15 6:17am

I am not getting how to calculate adjacent column in oracle
I am newbie in oracle.Please help

Yvan Rodrigues 8-Aug-15 11:47am

Show us your query.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Praveen_Kumar Gupta · Answer 1 · 2015-08-09T22:20:00

Try This

SQL

drop table t1;
create table t1
(
"id" number(10),
"name" varchar2(100),
"age" number(10),
"gender" char(1),
"BALANCE" number(10)
);
insert into t1 values(1001,'john',10,'M',10);
insert into t1 values(1002,'Meena',5,'F',0);
insert into t1 values(1003,'Nikh',11,'M',0);
insert into t1 values(1004,'divs',7,'F',0);
insert into t1 values(1005,'neha',4,'F',0);
select * from t1;

And Create A Procedure like this

SQL

declare 
initBal t1."BALANCE"%type;
begin
select "BALANCE" into initBal from t1 where rownum<=1 order by "id";
for c in (select rownum slno, "id","name","age","gender","BALANCE" from t1 order by "id")
loop
if c.slno >1 then
 if c."gender" = 'M' then
 initBal :=  initBal+c."age";
 elsif c."gender" = 'F' then
 initBal :=  initBal-c."age";
 end if;
end if;
dbms_output.put_line(c."id"||' , '||c."name"||' ,  '||c."age"||' ,  '||c."gender"||' ,  '||initBal);
end loop;
end;

Here we can see output on dbms output window

Divya Malpani · Answer 2 · 2015-08-11T18:35:00

Solution 3

I did in this way

SQL

create or replace procedure t1_d11
as
b  int :=0;
cursor test is select * from test_divs order by id;
begin 
for p in test
loop
if p.gender = 'M' then
b :=p.age+b;
else
b :=b-p.age;
end if;
update test_divs set balance = b where id=p.id;
end loop;
end;

Posted 11-Aug-15 18:35pm