Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Elroacho (Programmer) (OP)
30 May 04 17:35
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:

CODE

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 (Programmer)
6 Aug 04 7:07
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close