ETL fails on trigger error for SW_INSP_REPORT insert (duplicate GUIDs for SW_TRACK_ID in SW_TRACKING?)

[gerrykelly]

With our new data, the ETL fails on one of its first steps where it populates SW_INSP_REPORT:

From the following SQL, insert into SW_INSP_REPORT (objectid, SW_INSP_REPORT_ID, INSP_DATE,INSP_STATUS,sw_track_id,REPORTING_YEAR) (select objectid,trim(SW_INSP_REPORT_ID),INSP_DATE,INSP_STATUS,sw_track_id,REPORTING_YEAR from ETL_POND_INSP_FOR_SWI_RPT_MAX )

you'll see: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "NPDES.BEFORE_ADD_SW_INSP_REPORT", line 5 ORA-04088: error during execution of trigger 'NPDES.BEFORE_ADD_SW_INSP_REPORT'

I finally figured out it is because with our new data, the trigger is finding duplicate SW_TRACK_ID GUIDs.

This is true in SW_TRACKING (i.e. not just a product of the view I use for insert.

(the duplicate is found by) select sw_track_id, count(sw_track_id) from sw_tracking group by sw_track_id having count(sw_track_id) > 1

This might be an isolated case, and apologies for assigning all three of you, but it needs to be fixed in the data for this specific instance. More so we need to know how a duplicate GUID was ever created and prevent that in all cases.

(Also a duplicate BMP_ID in BMPs, maybe related?)

[gerrykelly]

BTW, this also will cause errors in the ETL anywhere there are duplicate GUIDs for the key IDs that are used to query back to the original records to get the new MDE-IDs for the export (and use those IDs to query related records), but this would not produce an error like in the insert/trigger-failure ... you just would not know. (so we are actually getting lucky catching is on the first ETL run after the data load)

[leeensminger]

Note to self for testing - duplicate BMPS.BMP_ID is a valid use case - for conversions. Need to test.

[leeensminger]

This is a data issue