| %rowtype in a cursor [message #37887] |
Tue, 05 March 2002 05:50  |
Jenn
Messages: 6
Registered: November 2001
|
Junior Member |
|
|
Hi,
I am trying to do the following, and the procedure runs clean, but nothing is updated.
Here is the cursor:
declare
v_oldbadge badgeassign%rowtype;
v_newbadge badgeassign%rowtype;
cursor badgeassign_cursor is
select * from badgeassign;
begin
OPEN badgeassign_cursor;
LOOP
FETCH badgeassign_cursor INTO v_newbadge;
EXIT WHEN badgeassign_cursor%NOTFOUND;
IF v_newbadge.personid = v_oldbadge.personid and v_newbadge.badgeassignid = v_oldbadge.badgeassignid
THEN
UPDATE badgeassign
set badgeassign.effectivedtm = v_oldbadge.effectivedtm
where badgeassign.badgeassignid = v_newbadge.badgeassignid;
DELETE from badgeassign where badgeassign.badgeassignid = v_oldbadge.badgeassignid;
END IF;
v_oldbadge := v_newbadge;
END LOOP;
CLOSE badgeassign_cursor;
end;
/
It appears as if I never enter the IF statement.
Any suggestions?
Jenn
|
|
|
|
| Re: %rowtype in a cursor [message #37890 is a reply to message #37887] |
Tue, 05 March 2002 06:00  |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
v_oldbadge has not been been assigned a value for the first time loop is executed (i.e. for first row of the cursor).
and from next row onwoards,the statement "v_oldbadge := v_newbadge;" is executing for each row.i do not think ever the if statement will evaluate to TRUE.
change ur logic.
by the way, what u want do ? what is ur idea behind the logic ?
|
|
|
|
| Re: %rowtype in a cursor [message #37892 is a reply to message #37887] |
Tue, 05 March 2002 06:13 |
Jenn
Messages: 6
Registered: November 2001
|
Junior Member |
|
|
Thats what I was thinking, but I can't figure out where I can assign v_oldbadge... I tried putting it in the fetch, but that gave an error. Have any ideas of where I can stick in the following
v_oldbadge := select * from badgeassign where badgassignid = 1 (this will be the first row in the table)
The logic:
I want to combine adjacent rows that have the same
PERSONID and BADGENUM into one row. The info that im combining is start(effectivedtm) and end dates.
Thanks!
Jenn.
|
|
|
|
| Re: %rowtype in a cursor [message #37896 is a reply to message #37887] |
Tue, 05 March 2002 08:40 |
Jenn
Messages: 6
Registered: November 2001
|
Junior Member |
|
|
I found a way to make my original code work! Here is what I came up with. Thanks for your help!
declare
v_oldbadge badgeassign%rowtype;
v_newbadge badgeassign%rowtype;
cursor badgeassign_cursor is
select * from badgeassign;
begin
OPEN badgeassign_cursor;
LOOP
FETCH badgeassign_cursor INTO v_newbadge;
EXIT WHEN badgeassign_cursor%NOTFOUND;
IF v_newbadge.personid = v_oldbadge.personid and v_newbadge.badgenum = v_oldbadge.badgenum
THEN
UPDATE badgeassign
set badgeassign.effectivedtm = v_oldbadge.effectivedtm
where badgeassign.badgeassignid = v_newbadge.badgeassignid;
v_newbadge.effectivedtm := v_oldbadge.effectivedtm;
DELETE from badgeassign where badgeassign.badgeassignid = v_oldbadge.badgeassignid;
END IF;
v_oldbadge := v_newbadge;
END LOOP;
CLOSE badgeassign_cursor;
end;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
