AnsweredAssumed Answered

Selecting Min Value when Lookup Step Returns Null

Question asked by B167W1XV on Dec 12, 2018

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?

 

Thanks!

Outcomes