CODEWITHSUNDEEP
sqloracle-databaseloopsplsql
JB999
JB999

Reputation: 507

PL/SQL Assign number on each iteration

I'm trying to assign a value for each one of my items. These are my columns:

Manager_id = contains a changing number of managers Sales_reps = list of the reps under the managers

What I'm trying to do is to loop throught every manager and assign a value (starting with 1) to each rep under it, like this:

Manager_1:
Rep_1
Rep_2

Manager_2:
Rep_1
Rep_2
Rep_3
Rep_4

Manager_3:
Rep_1
Rep_2

Etc.

I've tried basic loops, for loops, associative arrays but nothing seems to work. Could someone give me a hand please?

Thanks!

Upvotes: 0

Views: 75

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Sounds like a nested loops issue. Something like this:

begin
  for cur_m in (select manager_id, manager_name, rownum rn from managers) 
  loop
    dbms_output.put_line(cur_m.manager_name ||'_'|| cur_m.rn);

    for cur_e in (select employee_name, rownum rn from employees
                  where manager_id = cur_m.manager_id
                 )
    loop
      dbms_output.put_line(cur_e.employee_name || cur_e.rn);
    end loop;
  end loop;
end;

To answer question you posted as a comment: if you want to restart sequence per each manager, change the inner loop to

    for cur_e in (select employee_name, 
                    row_number() over (partition by manager_id order by employee_name) rn 
                  from employees
                  where manager_id = cur_m.manager_id
                 )

Upvotes: 2

Related Questions