Hi, I have a view that is composed of an other view and some more columns : DROP VIEW IF EXISTS RBPcvViewP; CREATE VIEW RBPcvViewP AS SELECT RBPcvViewF.*, '' as ZSocDefinition, SocTitle, SocSIRET, SocNAF, SocNIF, SocCapital, SocRCS, SocCGV, SocMentionOblig, SocAssur, SocWeb, SocGPicID, '' as ZSocFAdresse, A4.AdrRue1 as SocFAdrRue1, A4.AdrRue2 as SocFAdrRue2, A4.AdrRue3 as SocFAdrRue3, A4.AdrZip as SocFAdrZip, A4.AdrCity as SocFAdrCity, A4.AdrRgn as SocFAdrRgn, A4.AdrLand as SocFAdrLand, '' as ZSocMoc, MOC.MocTel as SocGMocTel, MOC.MocFax as SocGMocFax, MOC.MocPort as SocGMocPort, MOC.MocMail as SocGMocMail FROM RBPcvViewF, SOC, ADR A4, MOC WHERE RBPcvViewF.PcvGSocID = SOC.SocID AND A4.AdrID = SocFAdrID AND MocID = SocGMocID; RBPcvViewF is a view. Sometimes I need to get all the column in the RBPcvViewP, so I do this select : SELECT * from SYS.SYSCOLUMNS where creator='DBA' and tname= 'RbPcvViewP' order by colno When I add a column in the RBPcvViewF, the select above don't return the "new" column of the RBPcvViewF, I am forced to alter the RBPcvViewP (with no change) to have the column returned by the select above. My question : is there a way that the SYSCOLUMNS (when tname = 'RbPcvViewP') to be automatically updated when I add a column in the RbPcvViewF ? |
What version do you use?
AFAIK, starting with SQL Anywhere 10, the "View dependency" feature should automatically adapt a view when it is using a SELECT * from a base table (*) and the base table is enhanced (and in contrast, it would mark the view as INVALID if it references columns of the base table that have been dropped or renamed)...
() Note, I'm not sure whether this applies to every usage of "SELECT " in the view's select list or only if the list refers to only one single "SELECT *" from one base table with no other columns seleted.)
See here for a question I once had on that topic...
I use SQLA 17.
In your topic your view is based on select * from table, but here mine is based on select view.* , xx, zz, aa from ... It is a different so I don't think dependency applies in the same way.
So what does the following show:
Does it list RBPcvViewP?
Yes it does.