Select * does not pick up new fields added to table Why?

Database
Fan

Select * does not pick up new fields added to table Why?

create view grp_cdm_ch_views.V_REF_CONTACT_STATUS_ID as

(select * from grp_cdm_ch_table.REF_CONTACT_STATUS_ID)

 

I have added 2 new columns to table REF_CONTACT_STATUS_ID however the clients are not seeing

The 2 new columns that were added.  Should I recreate the view?
we thought since the view is created as Select *  it should pick up all the changes… can someone provided a little more knowledge on this topic. The changes are not being picked-up

Tags (1)

Accepted Solutions
Junior Contributor

Re: Select * does not pick up new fields added to table Why?

Do a SHOW QUALIFIED SELECT * FROM grp_cdm_ch_views.V_REF_CONTACT_STATUS_ID to see the resolved source code.

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: Select * does not pick up new fields added to table Why?

Yes, you must recreate the view to update the metadata information for the view.

 

If the view were automatically updated it might break processes / queries that were depending on the same number of coulmns beign there.

 

For this reason, the best practise is always to explicitly define columns in views.

 

thanks

 

dave

Junior Contributor

Re: Select * does not pick up new fields added to table Why?

Do a SHOW QUALIFIED SELECT * FROM grp_cdm_ch_views.V_REF_CONTACT_STATUS_ID to see the resolved source code.

Fan

Re: Select * does not pick up new fields added to table Why?

thank you everyone..  this is very good info..  I was not aware...  I need to review all the tables that were modified.
and re-create the view.

Junior Supporter

Re: Select * does not pick up new fields added to table Why?

Hi David,

Why dont we have a flag somewhere (dbc.tables) to say that this view defn is invalied now ? We come to know this when we run a sel * from view that the new column is added. I believe oracle has a concept of invalid views which is not available in TD. This question comes to us from mant TD users who were oracle users earlier.

--samir