To reject the records that violates Referential Integrity
Moderators: chulett, rschirm, roy
To reject the records that violates Referential Integrity
My Parallel job is getting aborted with "ORA:-02291-Parent Key Not found" error while inserting into the table.This error is since i am inserting the values into a column which is defined as a foriegn key of other column.If the values am inserting does not match the values of the referenced column i am getting this error.I want the job to get finished by rejecting the records which violates the referential integrity.Please provide a solution asap.
2 B 1 4 ALL
Re: To reject the records that violates Referential Integrit
Most DSXchange members will find this statement rude, even though it starts with a 'please'. Yeah, they will tell you that "we don't do asap here. If it is urgent, then someone may oblige if an appropriate fee is paid".laxman.ds wrote:Please provide a solution asap.
To answer your question, I think it is a bad design that you have rows that violate foreign key constraints. Such rows should never have made it to the target. However, if you are handling late arriving dimensions, then there are special ways of handling it.
gateleys
I tried this way,but the job is getting aborted with the same error.The Error is as following:Maveric wrote:For the upsert mode property, you can use "update only option" and give the insert statement. Also you can set the Output Reject Record to true and capture them from the reject link. Might work. Try it and let us know if it works.
TgtPartyInd,0: Open failed for update:
update is: INSERT
INTO
CCRPROD.PARTY_IND
(DATE_OF_BIRTH, GENDER_CD, MARITAL_STATUS_CD, EDUCATION_CD, PARTY_ID, ETL_CRT_DATE, ETL_CRT_JOB)
VALUES
( :DATE_OF_BIRTH, :GENDER_CD, :MARITAL_STATUS_CD, :EDUCATION_CD, :PARTY_ID, :ETL_CRT_DATE, :ETL_CRT_JOB)
sqlcode is: -2291
esql complaint: ORA-02291: integrity constraint (CCRPROD.SYS_C002996) violated - parent key not found
2 B 1 4 ALL
Re: To reject the records that violates Referential Integrit
To answer your question, I think it is a bad design that you have rows that violate foreign key constraints. Such rows should never have made it to the target. However, if you are handling late arriving dimensions, then there are special ways of handling it.[/quote]
Could you help me with a special way for handling it?
Could you help me with a special way for handling it?
2 B 1 4 ALL
What about setting the option Output Reject Records to true?
Here is the link with the solution to your issue: (Vincent's blog) [http://wiki.ittoolbox.com/index.php/Par ... led_upsert.]
Here is the link with the solution to your issue: (Vincent's blog) [http://wiki.ittoolbox.com/index.php/Par ... led_upsert.]
laxman.ds wrote:This is not a bright option since i have 7,8 foriegn keys in my table.is there any option to reject the records that does not meet the foriegn key constraint?
1. Get the foreign keys from your target, concatenate them and compute CRC on the concatenated value.laxman.ds wrote:This is not a bright option since i have 7,8 foriegn keys in my table.is there any option to reject the records that does not meet the foriegn key constraint?
2. Populate a hashed file with this CRC value as the key.
3. Repeat steps 1 and 2 with your input rows as well.
4. Perform a lookup based on the CRC value, and pass only those rows to target if the lookup returns a match, else pass the row via a reject link to a reject file.
gateleys
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: