topic Re: ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found. in SAS Programming

ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found.

kuppusamy — Tue, 26 Jun 2018 08:00:07 GMT

Here, i know child record is there but parent record not found. now i need to know how to find the child record that do not have parent.

NOTE: Appending WORK.TEMP_MAP to ECM_DB.INCIDENT_UDF_NUM_VALUE.
NOTE: There were 1000 observations read from the data set WORK.TEMP_MAP.
NOTE: 999 observations added.
NOTE: The data set ECM_DB.INCIDENT_UDF_NUM_VALUE has . observations and 6 variables.
ERROR: ERROR: ERROR: ORACLE execute error: ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not
found. With the occurrence of the above ERROR, the error limit of 1 set by the ERRLIMIT= option has been reached.
ROLLBACK has been issued(Any Rows processed after the last COMMIT are lost).

Total rows processed: 1
Rows failed : 1
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
VERSION 6.3M1
ERROR: FCF: IEM: Unable to append to INCIDENT_UDF_NUM_VALUE
1328
1329
1330
1331 GOPTIONS NOACCESSIBLE;
1332 %LET _CLIENTTASKLABEL=;
1333 %LET _CLIENTPROCESSFLOWNAME=;
1334 %LET _CLIENTPROJECTPATH=;
1335 %LET _CLIENTPROJECTNAME=;
1336 %LET _SASPROGRAMFILE=;
1337
1338 ;*';*";*/;quit;run;
1339 ODS _ALL_ CLOSE;

Re: ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found.

Kurt_Bremser — Tue, 26 Jun 2018 08:16:11 GMT

Do a join with the "parent" table:

proc sql;
create table test as
select a.* from
work.temp_map a left join ecm_db.parent b
on a.key = b.key
where b.key is missing;
quit;

Where "parent" is the reference table and "key" is your unique key for the violated constraint.

Re: ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found.

kuppusamy — Tue, 26 Jun 2018 11:44:24 GMT

The dataset test getting all records. .

Re: ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found.

Kurt_Bremser — Tue, 26 Jun 2018 12:28:25 GMT

@kuppusamy wrote:

The dataset test getting all records. .

Then I suggest you get in touch with your Oracle database admins/designers and have them check your data and code against their constraints.

Re: ORA-02291: integrity constraint (ECMUSER.INCUDFNUMVAL_FK1) violated - parent key not found.

kuppusamy — Tue, 26 Jun 2018 12:53:46 GMT

Thank you for your response,

that error is because of risk assessment process(its completed with wrong input). now i rectified that and process running smoothly...