update that does not update with the correct value 1

[diehippy]

Hi All,

I was wondering if you could help me I have created a pl sql script that when run, will update a table with some values then update the tables that the values came from with either a success or failure except it is just updating every record the same either success or failure and not a combination of both depending on the restrictions I have added. I am not sure why here is the pl sql I used

CREATE OR REPLACE package sdc_ks_achieve_upload is

PROCEDURE sdc_upload_results;
PROCEDURE sdc_UPDATE_ETEST_RESULTS;


end;
/

CREATE OR REPLACE package body sdc_ks_achieve_upload IS

PROCEDURE sdc_upload_results IS
v_program_unit VARCHAR2(60) := 'sdc_ks_achieve_upload.sdc_upload_results';
n_error_code NUMBER(30);
v_error_message varchar2(150);


BEGIN

INSERT INTO sdc_keyskill_achievement_temp
(ocr_candidate_no, person_code, forename, surname, gender, date_of_birth, scheme_code, qual_code, scheme_title, keyskill_level, result, result_date, centre_no, dated_created)
SELECT
se.ocr_candidate_no,
p.person_code,
se.forename,
se.surname,
se.gender,
To_Date(se.date_of_birth, 'DD/MM/YYYY') date_of_birth,
se.scheme_code,
CASE WHEN se.scheme_code = '03794' AND se.unit_key_skills_level = 1 THEN '1000323X'
WHEN se.scheme_code = '03794' AND se.unit_key_skills_level = 2 THEN '10003071'
WHEN se.scheme_code = '03795' AND se.unit_key_skills_level = 1 THEN '10003265'
WHEN se.scheme_code = '03795' AND se.unit_key_skills_level = 2 THEN '10003253'
WHEN se.scheme_code = '03795' AND se.unit_key_skills_level = 3 THEN '10003241'
WHEN se.scheme_code = '03793' AND se.unit_key_skills_level = 1 THEN '10003289'
WHEN se.scheme_code = '03793' AND se.unit_key_skills_level = 2 THEN '10003290' ELSE null END qual_code,
se.scheme_title,
se.unit_key_skills_level,
se.result,
se.result_date,
se.centreno,
Trunc(SYSDATE)
FROM sdc_etest_ach_upload se,
people p
WHERE
To_Char(se.ocr_candidate_no) = p.rsa_number;

COMMIT;

EXCEPTION
WHEN others THEN
rollback;
n_error_code := SQLCODE;
v_error_message := SUBSTR('Insert into SDC_KEYSKILL_ACHIEVEMENT_TEMP = ' || SQLERRM(n_error_code), 1,150);
alex_error_manager.log_error_raise_no_app_error(v_program_unit, n_error_code, v_error_message, null);


END sdc_upload_results;


PROCEDURE sdc_UPDATE_ETEST_RESULTS IS
v_program_unit VARCHAR2(60) := 'sdc_ks_achieve_upload.sdc_update_etest_results';
n_error_code NUMBER(30);
v_error_message varchar2(150);

BEGIN


for cur_etest_res_upload IN ( SELECT
e.person_code,
e.forename,
e.surname,
e.date_of_birth,
e.unit_instance_code,
e.qual_code,
e.cuio_id,
e.calocc_code,
e.progress_status,
e.exam_occurrence_id,
e.board_code,
e.exam_code,
e.board_occurrence,
e.first_grade,
e.passed,
e.updated_by,
e.updated_date,
kt.person_code k_person_code,
kt.forename k_forename,
kt.surname k_surname,
kt.date_of_birth k_date_of_birth,
kt.ocr_candidate_no,
kt.scheme_code,
kt.scheme_title,
kt.keyskill_level,
kt.qual_code k_qual_code,
kt.result,
kt.result_date
from
(SELECT
distinct
p.person_code,
p.forename,
p.surname,
p.date_of_birth,
pu.unit_instance_code,
qa.qual_code,
pu.uio_id cuio_id,
pu.calocc_code,
pu.progress_status,
ee.exam_occurrence_id,
ee.board_code,
ee.exam_code,
ee.board_occurrence,
ee.first_grade,
ee.passed,
ee.updated_by,
ee.updated_date
FROM
people p,
people_units pu,
people_units_special pus,
unit_instance_occurrences uio,
uio_qual_aims qa,
exam_occurrence_uios eou,
exam_occurrences eo,
exam_entries ee
WHERE
p.person_code = pu.person_code
AND pu.uio_id = uio.uio_id
AND uio.uio_id = qa.uio_id
AND qa.default_qa = 'Y'
AND pu.id = pus.people_units_id
AND pus.funding_year = (SELECT funding_year FROM funding_years WHERE SYSDATE BETWEEN start_date AND end_date AND fes_active = 'Y')
AND pu.unit_type = 'R'
AND pu.calocc_code = (SELECT occurrence_code FROM calendar_occurrences WHERE SYSDATE BETWEEN start_date AND end_date AND occurrence_code NOT LIKE '%X')
AND pu.progress_status = 'A'
AND uio.fes_user_5 = 'KSF'
AND ee.board_code = 'RSA'
AND ee.board_occurrence = 'ETEST0910'
AND uio.uio_id = eou.uio_id
AND eou.exam_occurrence_id = eo.id
AND pu.person_code = ee.person_code
AND eo.exam_code = ee.exam_code
AND eo.board_code = ee.board_code
AND eo.board_occurrence = ee.board_occurrence

ORDER BY 3) e,
(SELECT
skt.person_code,
skt.forename,
skt.surname,
skt.date_of_birth,
skt.ocr_candidate_no,
skt.scheme_code,
skt.scheme_title,
skt.keyskill_level,
skt.qual_code,
Decode(skt.result, 'Pass','PA', 'FL') result,
skt.result_date
FROM
sdc_keyskill_achievement_temp skt
WHERE
skt.dated_created = Trunc(SYSDATE)
)kt
WHERE
kt.person_code = e.person_code
AND kt.qual_code = e.qual_code)


LOOP

update exam_entries ee
SET ee.first_grade = cur_etest_res_upload.result, ee.updated_by = 'FES', ee.updated_date = SYSDATE
WHERE
ee.exam_code = cur_etest_res_upload.exam_code
AND ee.person_code = cur_etest_res_upload.person_code
AND ee.board_code = cur_etest_res_upload.board_code
AND (ee.first_grade = 'FL' OR ee.first_grade is NULL)
AND ee.board_occurrence = cur_etest_res_upload.board_occurrence ;
COMMIT;



UPDATE sdc_keyskill_achievement_temp skt1
set skt1.updated_date = SYSDATE, skt1.successfully_updated =
(SELECT CASE WHEN ee1.updated_by = 'FES' AND Trunc(ee1.updated_date) = Trunc(SYSDATE) THEN 'Y'
WHEN ee1.first_grade = 'PA' AND ee1.updated_by <> 'FES' THEN 'N' ELSE 'N' END
FROM exam_entries ee1
WHERE
ee1.exam_code = cur_etest_res_upload.exam_code
AND ee1.person_code = cur_etest_res_upload.person_code
AND ee1.board_code = cur_etest_res_upload.board_code
AND ee1.board_occurrence = cur_etest_res_upload.board_occurrence );
COMMIT;



END LOOP;



END sdc_UPDATE_ETEST_RESULTS;

END;
/

Any help would be most appreciated

Many Thanks

Diehippy

 

[Dagon]

update exam_entries ee
SET ee.first_grade = cur_etest_res_upload.result, ee.updated_by = 'FES', ee.updated_date = SYSDATE

So it looks as if you are always setting updated_by to 'FES' and updated_date to SYSDATE.

Your case statement is:

CASE WHEN ee1.updated_by = 'FES' AND Trunc(ee1.updated_date) = Trunc(SYSDATE) THEN 'Y'
...

Since updated_by is always FES and updated_date will always be today's date, this condition will always be TRUE so you will always get the same answer.

For Oracle-related work, contact me through Linked-In.

 

[diehippy]

Hi Dagon,

Thanks for the reply,

I have restricted the first update to only update where first grade = 'FL' or first_grade is null so any other data should come back as 'N' if it has not updated due to this restriction. As I understand it.

Many Thanks

Diehippy

 

[diehippy]

Hi Dagon,

It is possible, I need to setup the second update with a seperate cursor to get the correct values in the second update. You thoughts will be most appreciated

Many Thanks

Mark

 

[Dagon]

It's certainly possible, but without knowing a lot more about the application I couldn't comment on whether it's necessary. I would say that, if possible, you should try to keep the updates within the same loop to guarantee that the same set of records are being updated.

For Oracle-related work, contact me through Linked-In.

 

[diehippy]

Thanks Dagon,

I know that I am on the right track, just need to figure out the update

Many Thanks

Mark