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.

 

[Elroacho]

I know the above code isn't perfect but I'm just trying to get accross what I'm tryign to do which is crate a script that will run a select stament into a temp store then let me step through the records, apply some logic and at some point save an assembled record based on that logic into a table.

Cheers,
Kevin.

 

[dnoeth]

Your descrioption sounds like some kind of aggregation without need for a cursor. Could you please post the expected result set from your test data.

select
Staff_ID,
Surname,
max(Salary),
max(case when BenCode = 'Bonus' then Salary end)
from old_tab
group by
Staff_ID,
Surname
;

CASE is ANSI SQL, most DBMSes support it or a similar syntax, eg. IF or DECODE

Dieter

 

[Elroacho]

Hi,

The output would look something like this....

Staff_ID| Surname| Salary | BenCode |
123......| Smith.....| 17,000 | Bonus...|
124......| Jones.....| 13,000 | Bonus...|
125......| Brown....| 13,000 | Bonus...|

Cheers.
Kevin

 

[mrdenny]

Kevin,
The output of the last column would be the max bonus received if there was a bonus. If there was no bonus then the field will be null.

Denny

--Anything is possible. All it takes is a little research. (Me)