1

I'm wondering how a condition in a commonly used WHERE clause can be abstracted away so that it can be safely changed.

Say I have data like

ColA | ColB | ColC
==================
  1  |   2  | ABC 
  1  |   3  | ABD 
  1  |   4  | ABE 
  2  |   2  | ACC 
  3  |   2  | ACD
  4  |   2  | ACE

I have an example query something like

SELECT ColA, ColB FROM MyTable WHERE ColC LIKE 'AB%'

This clause appears in many selections of varying complexity.

The problem I have is that the ColC codes are going to be changed incrementally into different values, e.g. ABE may be changed to ZBE.Throughout this period, the existing query results have to remain the same - i.e. ABC, ABD and ABE would have to become ABC, ABD and ZBE. Then ABD may change to ZBF and so on. Oh, and the database must have minimal down-time.

My gut instinct would be to change all occurrences of this clause into a function now when time-pressure is low, so that when data starts being transformed, I can change that function into whatever hideous thing it needs to be without having to worry about making lots of complex, messy changes quickly.

However, I've been reading up and come across many recommendations that I shouldn't be doing this as it will be inefficient (which I can totally see).

So, what would be the best way to do this? If I have to accept a period of inefficiency during this transition period, then so be it, but I'd rather not.

Thanks.

1 Answer 1

0

OK 5 years later, here's a possible solution.
If this is your database and you are allowed to change its structure, I would

  1. Create 2 new fields; OldC and NewC.
  2. Update all OldC values with the value of ColC.
  3. When ready to calculate, update all NewC values with the new value.
  4. When ready to migrate, update all ColC with NewC.

If during this time, you need to know whether ColC is the old or new value, compare it to the 2 new fields.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.