We're working on a migrating a legacy database and working through data integrity issues in the old system.
- Multiple organizations are represented in the database (org_id)
- I need to perform a lookup using org_id and a date-related field against a period reference table in the new database, which has already been populated
- The lookup returns a period_id, which is required in the target table (subject)
Each subject record should have a valid org_id and period_id specified. Unfortunately, due to lack of referential integrity in the legacy system, this is not the case and some org_id and period_id combinations for the subjects do not exist. The period_id returns nothing, and therefore the insert fails due to a not-null constraint violation.
In this scenario, I would like to migrate the subject record by set it's period_id to the earliest existing period_id for the org_id.
I can use a filter step to identify those records whose period_id is null.
My question is - which step(s) can I use to run a select statement to return the min(period_id) using the org_id as an input parameter and map this to the period_id field?
I've looked at a couple of options but run into challenges:
- The Table Input step doesn't appear to allow for the mapping of a value from a previous step as an input parameter - so I can't do something like "select id from period where org_id = ? and period_id = (select min(period_id) from period_table where org_id = ?)
- The Execute SQL statement step allows for a field to passed in as a parameter, however, doesn't appear to be intended to return values
I believe I could write a function and use the "Call DB Procedure" step but I was trying to avoid writing database code for the migrations.
Is there another step or combination of steps I can use?