| No data found scenario that I cannot understand why [message #10200] |
Thu, 08 January 2004 09:12  |
Andy G
Messages: 25
Registered: May 2003
|
Junior Member |
|
|
I didn't think I would have so much trouble with this task but I do an am not sure why.
We have a table here that has a list of employees, basically the employees have two seperate records one record is associated to there People soft account and then the other record is the oracle user name that we assign them...the relation is a emp_id that is the same...
so what is happeneing is the data is being populated during the evening with a script if an employee is terminated the record in relation to people soft gets updated with an end date...
However I want to create a simple PL/SQL script that will run through and add an end date if to the oracle user name records if the people soft record has an end date and the oracle user name doesn't...
am I make sense so far...
So I came up with the folloing script
Declare
v_emp_id number(8);
v_date_end date;
v_counter number(8);
Cursor er_cleanup is
select emp_id
from emp_table
where ref_type = PS (people soft)
and external_code = 10
and date_end is null ;
BEGIN
v_counter := 0;
open end_date
loop
fetch end_date
v_emp_id ;
exit when end_dated%notfound;;
select date_end into v_date_end
from emp_table
where emp_id = v_emp_id
And ref_type = OU (Oracle user)
and external_code = 30
and date_end is not null;
If v_date_end is not null
update emp_table
set date_end = sysdate
where emp_id = v_emp_id
and ref_type = PS (people soft)
and external_code = 10;
Else
Null;
End if;
v_counter := v_counter +1;
end loop;
close end_date
commit;
END;
It's giving me a no data found for the
select date_end into v_date_end
I figured that some of the dates are null but they would just be input as such....
hmm any ideas on what im doing wrong and how I cna better approach this
Thanks for any help.
|
|
|
|
| Re: No data found scenario that I cannot understand why [message #10203 is a reply to message #10200] |
Thu, 08 January 2004 10:14  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Always use a single SQL statement when possible.
In your example, I'm not sure why you are updating the row that has a ref_type = 'PS'. Your description said that the PS rows would have a date, but the Oracle user row would not - I thought the Oracle row was the one you wanted to update with the PS date.
update emp_table e1
set date_end = sysdate
where external_code = 10
and ref_type = 'OU' -- whatever your code is that identifies this row as the Oracle user row as opposed to the PS row
and date_end is null
and exists (select null
from emp_table e2
where e2.emp_id = e1.emp_id
and e2.external_code = e1.external_code
and e2.ref_type = 'PS'
and e2.date_end is not null);
This will update any Oracle user rows that do not have a date_end value if there is a corresponding (by emp_id) PS row that does have a date_end value.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
