Cursors

[Elroacho]

Ok Guys, See if you can help me on this one.

What I need to do is run a SQL query which I want to store in a temp table (possibly using a cursor) I then want to step through each record in the temp table and store some of the field an some local variables. At some point when I have a full records worth of variables I then want to enter my variables into a proper table as a single record. E.G.

After I run my initial query I get the following temp table:

Staff_ID | Surname | Salary | BenCode |
123 | Smith | 15,000 | Car |
123 | Smith | 16,000 | Bonus |
123 | Smith | 17,000 | Pen |
124 | Jones | 13,000 | Bonus |
124 | Jones | 10,000 | Pen |
125 | Brown | 12,000 | Bonus |
125 | Brown | 13,000 | Pen |
125 | Brown | 11,000 | Car |

The above has been simplified as what I’m really working on would take forever to write but the principal remains. What I want to do next is step through this table and inserting the ID, Surname; The HIGHEST Salary for that employee and Bonus if it exists in a record for them.

PSUEDO version:

DELCLARE TestCursor CURSOR AS
SELECT STAFF.Staff_ID, STAFF.Surname, STAFF.Salary, BENEFIT.BenCode
FROM STAFF, BENEFIT
WHERE STAFF.Staff_ID = BENEFIT.Staff_ID;

DELCARE 	VARStaffID 	AS NUMBER,
DELCARE 	VARSurname	AS VARCHAR(25),
DELCARE 	VARSalary	AS NUMBER,
DELCARE 	VARBenCode	AS VARCHAR(25),

VARStaffID = NULL;
For each TestCursor record {
    /*First record for that employee then copy all fields (this will only be run for the first
       record*/
    If VARStaffID = NULL then { 
	VARStaffID 	= TestCursor.Staff_ID
	VARSurname 	= TestCursor.Surname
	VARSalary 	= TestCursor.Salary
	If (TestCursor.BenCode = ‘Bonus’) { /*Only copy this field if ‘Bonus’*/
		VARBenCode = TestCursor.BenCode
	}
else {
	VARBenCode = NULL
}

    }
   /*If another record for current employee*/
   Else if VARStaffID =  TestCursor.Staff_ID { 
	If TestCursor.Salary > VARSalary {
		VARSalary  = TestCursor.Salary
	}
   /*This will be first record of new employee so copy vars and start again*/
   Else {
	Copy all variables to a table as a new record
	VARStaffID 	= TestCursor.Staff_ID
	VARSurname 	= TestCursor.Surname
	VARSalary 	= TestCursor.Salary
	If (TestCursor.BenCode = ‘Bonus’) { /*Only copy this field if ‘Bonus’*/
		VARBenCode = TestCursor.BenCode
   }
Loop;

COMMIT;

I hope this isn’t to long-winded, I have tried to simplify it.

Cheers,
Kevin.

 

[donvittorio]

This reply probably comes too late to help Kevin, but I thought I would just point out that you don't really need to select all records and then iterate through them picking out the information you want. Surely it's better to select only the information you want in the first place?
In this case the following would give you what you want

select staff_id, surname, 'Bonus' bonus_code, max(salary)
from staff s1
where exists (select * from staff s2 where s2.staff_id = s1.staff_id and bencode = 'Bonus')
group by staff_id, surname
UNION
select staff_id, surname, ' ' bonus_code, max(salary)
from staff s1
where not exists (select * from staff s2 where s2.staff_id = s1.staff_id and bencode = 'Bonus')
group by staff_id, surname

Steve