Does anyone have a nice query for matching a merged account number to the active account that it ultimately leads to (obviously an entry in T_MERGED may just lead you to another account that has since been merged) without using a loop?
Update: unless there is a good way, maybe T_MERGED should have a column called "current_id" that updates all previous rows related to the merged id with the new id.
Check out TA_AUDIT_TRAIL -- search for [action] = 'merged' and then [old_value] = <merged account number>. Be advised that, depending on the size of your [TA_AUDIT_TRAIL] table, you may need to optimize the query by adding a custom index.
Thanks, that works: ugly having to convert the text field, but definitely better than what I was doing.
Still think there should be a column in T_MERGED, though...
Oof. looking up by the old id is pretty ugly in that scenario (~ten seconds).