I have a stored procedure in which I have put this code block to attempt to catch any no data found errors and then continue to the next block in the procedure:
BEGIN
insert into VISITS_IN (particid, subjno, VPATID,VISITDT)
select distinct
act.siteid as PARTICID,
(act.siteid||'-'||act.patid) as SUBJNO,
act.patid as VPATID,
returninterpretations.returnvaliddate(V.STARTDATE)
from CCD_PATIENT act
FULL OUTER JOIN CCR_PROBLEMS v on act.SITEID = v.SITEID
where not exists (select RECORDID from VISITS_IN where returninterpretations.returnvaliddate(VISITDT)
between returninterpretations.returnvaliddate(v.STARTDATE) and returninterpretations.returnvaliddate(nvl(v.ENDDATE,sysdate)));
commit;
--catch no data found error and continue
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
Only the proc is still failing at this point with the error and is not continuing. Is there a better way to do this? The entire sproc looks like this:
CREATE OR REPLACE procedure OUTPATIENT.CCD_INSERT
AS
CURSOR FieldNames_Tab IS
select upper(destinationfield) from translation where upper(destinationtable) = 'VISITS_IN';
v_FieldName VARCHAR2(200);
CURSOR HasRecords IS
select rownum from visits_in where rownum = 1;
v_RowNum NUMBER;
--string variables for building update statements
v_SQL VARCHAR2(3000);
v_Translate VARCHAR2(2000);
--variables to hold ID values for specific record selection
v_StartID number;
v_MaxID number;
BEGIN
execute immediate 'alter session set NLS_DATE_FORMAT=''MM/DD/YYYY''';
--insert VISIT values first to allow for VISITID matching later
--last recordid before visit insert, to help determine which records to update later
open HasRecords;
fetch HasRecords into v_RowNum;
if HasRecords%FOUND then
select max(recordid) into v_StartID from visits_in;
else
v_StartID := 0;
end if;
close HasRecords;
--create base visit records from CCR_VISITS
BEGIN
insert into VISITS_IN (particid, subjno, VPATID,VISITID,VISITDT,NPI)
select distinct
act.siteid as PARTICID,
(act.siteid||'-'||act.patid) as SUBJNO,
act.patid as VPATID,
max(V.VISITID),
returninterpretations.returnvaliddate(V.VISITDT),
V.NPI
from CCD_PATIENT act
FULL OUTER JOIN CCR_VISITS v on act.SITEID = v.SITEID
group by act.siteid, (act.siteid||'-'||act.patid), act.patid, returninterpretations.returnvaliddate(V.VISITDT),V.NPI;
commit;
--catch no data found error and continue
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
--create base visit records from CCR_LABS where date does not exist in VISITS table yet
BEGIN
insert into VISITS_IN (particid, subjno, VPATID,VISITID,VISITDT)
select distinct
act.siteid as PARTICID,
(act.siteid||'-'||act.patid) as SUBJNO,
act.patid as VPATID,
max(V.LABID),
returninterpretations.returnvaliddate(V.LABDT)
from CCD_PATIENT act
FULL OUTER JOIN CCR_LABS v on act.SITEID = v.SITEID
where v.LABDT not in (select VISITDT from VISITS_IN where RECORDID >= v_StartID)
group by act.siteid, (act.siteid||'-'||act.patid), act.patid, returninterpretations.returnvaliddate(V.LABDT);
commit;
--catch no data found error and continue
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
--create base visit records from CCR_PROBLEMS where date does not exist in VISITS table yet
BEGIN
insert into VISITS_IN (particid, subjno, VPATID,VISITDT)
select distinct
act.siteid as PARTICID,
(act.siteid||'-'||act.patid) as SUBJNO,
act.patid as VPATID,
returninterpretations.returnvaliddate(V.STARTDATE)
from CCD_PATIENT act
FULL OUTER JOIN CCR_PROBLEMS v on act.SITEID = v.SITEID
where not exists (select RECORDID from VISITS_IN where returninterpretations.returnvaliddate(VISITDT)
between returninterpretations.returnvaliddate(v.STARTDATE) and returninterpretations.returnvaliddate(nvl(v.ENDDATE,sysdate)));
commit;
--catch no data found error and continue
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
--selection of new RECORDID values to ensure updating only of related records regardless of
--duplicate SUBJNO
select max(recordid) into v_MaxID from visits_in;
select min(recordid) into v_StartID from visits_in where recordid > nvl(v_StartID,0);
--loop through VISIT table fields, populating each one if records exist based on coding systems entered
--utilitzing sql stored in TRANSLATION table
If v_StartID is not null then
open FieldNames_Tab;
Loop
Fetch FieldNames_Tab into v_FieldName;
Exit when FieldNames_Tab%NOTFOUND;
select translationsql into v_Translate from translation where upper(destinationfield) = v_FieldName and upper(destinationtable) = 'VISITS_IN' and FORMATID =
(select f.formatid from siteformat f inner join ccd_patient pat on pat.siteformatid = f.siteformatid);
v_SQL := 'update VISITS_IN set '||v_FieldName||' = ('||v_Translate||') where RECORDID between '||v_StartID||' and '||v_MaxID;
execute immediate v_SQL;
commit;
End Loop;
close FieldNames_Tab;
End If;
--insert the rest of the stuff
--demographics insert
BEGIN
insert into demographics_in (PARTICID,SUBJNO,RECORDID,ERRFLAG,PATFNAME,PATMINIT,PATLNAME,SSN,DIE,DOD,PATID,DOB,GENDER,
RACENATIVEAM,RACEASIAN,RACEBLACK,RACENATIVEPACIFIC,RACEWHITE,RACEUTD,HISPANIC,DIEACS,DIESTROKE)
select distinct
pat.siteid as PARTICID,
(pat.siteid||'-'||pat.patid) as SUBJNO,
nvl((select max(RECORDID) from demographics_in) + 1,1) as RECORDID,
0,
pat.givenname as PATFNAME,
substr(pat.middlename, 1, 1) as PATMINIT,
pat.familyname as PATLNAME,
pat.ssn as SSN,
case nvl(dp.causeofdeath,'a') when 'a' then '2' else '1' end as DIE,
dp.deathdate as DOD,
pat.patid as PATID,
pat.dateofbirth as DOB,
case upper(nvl(pat.gendercode,'a')) when 'M' then 1 when 'F' then 2 else 3 end as GENDER,
case when exists(select vc.IVCODEID from CODESYSTEMVALUES vc where vc.codevalue = pat.racecode and vc.ivcodeid = 201) then 1
else NULL end as RACENATIVEAM,
case when exists(select vc.IVCODEID from CODESYSTEMVALUES vc where vc.codevalue = pat.racecode and vc.ivcodeid = 203) then 1
else NULL end as RACEASIAN,
case when exists(select vc.IVCODEID from CODESYSTEMVALUES vc where vc.codevalue = pat.racecode and vc.ivcodeid = 205) then 1
else NULL end as RACEBLACK,
case when exists(select vc.IVCODEID from CODESYSTEMVALUES vc where vc.codevalue = pat.racecode and vc.ivcodeid = 207) then 1
else NULL end as RACENATIVEPACIFIC,
case when exists(select vc.IVCODEID from CODESYSTEMVALUES vc where vc.codevalue = pat.racecode and vc.ivcodeid = 209) then 1
else NULL end as RACEWHITE,
case when exists(select vc.IVCODEID from CODESYSTEMVALUES vc where vc.codevalue = pat.racecode and vc.ivcodeid = 211) then 1
else NULL end as RACEUTD,
case when exists(select vc.IVCODEID from CODESYSTEMVALUES vc where vc.codevalue = pat.ethnicitycode and vc.ivcodeid = 213) then 1
else NULL end as HISPANIC,
dp.ACS as DIEACS,
dp.STROKE as DIESTROKE
from CCD_PATIENT pat
FULL OUTER JOIN (select pro.causeofdeath, pro.deathdate, iva.harvestcode as ACS, ivs.harvestcode as STROKE from ccr_problems pro
left join codesystem cs on pro.codesystem = cs.codesystem and nvl(pro.codesystemversion,'a') = nvl(cs.codeversion,'a')
left join codesystemvalues cva on cs.codesystemid = cva.codesystemid and cva.ivcodeid = 243 and pro.problemvalue = cva.codevalue
left join interpretationvalues iva on cva.ivcodeid = iva.codeid
left join codesystemvalues cvs on cs.codesystemid = cvs.codesystemid and cvs.ivcodeid = 247 and pro.problemvalue = cvs.codevalue
left join interpretationvalues ivs on cvs.ivcodeid = ivs.codeid
where upper(pro.causeofdeath) in (select upper(codevalue) from codesystemvalues where ivcodeid = 234)) dp on dp.causeofdeath = dp.causeofdeath;
commit;
--catch no data found error and continue
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
--family history insert
BEGIN
insert into familyhistory_in (PARTICID,SUBJNO,FHPATID,FHICD10,FHICD9)
select distinct
act.siteid as PARTICID,
(act.siteid||'-'||act.patid) as SUBJNO,
act.patid as PATID,
case nvl(upper(pro.codesystem),'a')
when 'ICD10' then PRO.PROBLEMVALUE
else NULL
end as MHICD10,
case nvl(upper(pro.codesystem),'a')
when 'ICD9' then PRO.PROBLEMVALUE
else NULL
end as MHICD9
from CCR_FAMILY_HISTORY pro, CCD_PATIENT act
where (case nvl(upper(pro.codesystem),'a')
when 'ICD10' then PRO.PROBLEMVALUE
else NULL
end is not null
or
case nvl(upper(pro.codesystem),'a')
when 'ICD9' then PRO.PROBLEMVALUE
else NULL
end is not null);
commit;
--catch no data found error and continue
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
--medical history insert
BEGIN
insert into MEDICALHISTORY_IN (PARTICID,SUBJNO,MHPATID,MHICD10,MHICD9)
select distinct
pro.siteid as PARTICID,
(act.siteid||'-'||act.patid) as SUBJNO,
act.PATID as PATID,
case nvl(upper(pro.codesystem),'a')
when 'ICD10' then PRO.PROBLEMVALUE
else NULL
end as MHICD10,
case nvl(upper(pro.codesystem),'a')
when 'ICD9' then PRO.PROBLEMVALUE
else NULL
end as MHICD9
from CCR_PROBLEMS pro, CCD_PATIENT act
where
(case nvl(upper(pro.codesystem),'a')
when 'ICD10' then PRO.PROBLEMVALUE
else NULL
end is not null
OR
case nvl(upper(pro.codesystem),'a')
when 'ICD9' then PRO.PROBLEMVALUE
else NULL
end is not null);
commit;
--catch no data found error and continue
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
--procedures insert
BEGIN
insert into PROCEDURES_IN (PARTICID,SUBJNO,PPATID,PVISITID, PROCDDT,PICD9,PICD10)
select distinct
act.siteid as PARTICID,
(act.siteid||'-'||act.patid) as SUBJNO,
act.patid as PATID,
(select min(v.visitid) from ccr_visits v where returninterpretations.returnvaliddate(v.visitdt) <= nvl(returninterpretations.returnvaliddate(pro.procdt), sysdate)) as VISITID,
pro.procdt as procdate,
case nvl(upper(pro.codesystem),'a')
when 'ICD10' then PRO.PROBLEMVALUE
else NULL
end as PICD10,
case nvl(upper(pro.codesystem),'a')
when 'ICD9' then PRO.PROBLEMVALUE
else NULL
end as PICD9
from ccr_procedures pro, CCD_PATIENT act
where
(case nvl(upper(pro.codesystem),'a')
when 'ICD10' then PRO.PROBLEMVALUE
else NULL
end is not null
OR
case nvl(upper(pro.codesystem),'a')
when 'ICD9' then PRO.PROBLEMVALUE
else NULL
end is not null);
commit;
--catch no data found error and continue
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
--medications insert
BEGIN
insert into MEDICATIONS_IN (PARTICID,SUBJNO,MPATID,MVISITID,MEDNAME,MEDDOSEUNIT,MEDDOSENUM)
select distinct
act.siteid as PARTICID,
(act.siteid||'-'||act.patid) as SUBJNO,
act.patid as PATID,
(select min(v.visitid) from ccr_visits v where returninterpretations.returnvaliddate(v.visitdt) <= nvl(returninterpretations.returnvaliddate(pro.enddate), sysdate)) as VISITID,
pro.productname as MEDNAME,
PRO.MEDDOSEUNIT,
PRO.MEDDOSENUM
from ccr_medications pro, CCD_PATIENT act;
commit;
--catch no data found error and continue
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
END CCD_INSERT;
Thanks,
Eva