On-prem Migration project dev to prod (views with derived tables)


(Bhanu Murthy) #1

Hi Team,

we are using on-prem instance for our development and now planning to migrate all the code from Dev to production(on-prem clustered environment)

We created so many views using derived tables and the issue is database names are different in Dev (ex: test_abc_database) and Production(ex:abc_database).

Am able to migrate all the code from Dev to prod but as the database names are different am getting an error in all the views (there are nearly 30 to 40 views created based on derived tables)

Is there a way to make it work by not going to each and every individual view and update it manually? (there are nearly 30 to 40 views created based on the derived tables)

Example View in my Dev

,sum(a11.LIEN_CNT) Lien_Cnt_3
from TEST_DOP.Esc_Pit_Agcy_Agg a11
join TEST_DOP.Cycle1 a12
on (a11.CYCLE_KEY = a12.CYCLE_KEY)

Example View in my Prod

,sum(a11.LIEN_CNT) Lien_Cnt_3
from **DOP.**Esc_Pit_Agcy_Agg a11
join DOP.Cycle1 a12
on (a11.CYCLE_KEY = a12.CYCLE_KEY)


(Izzy) #2

I bet you could just use the built-in find and replace feature in the IDE, in the top right search bar. You’d have to do it for each table name, but you can find and replace project-wide. If you’re feeling risky, you could even just find and replace join TEST_ and from test_ with join and from respectively…