|
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 ? |
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 28 Aug '19, 09:27
question was seen: 691 times
last updated: 28 Aug '19, 11:48
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:
SELECT t.table_name FROM SYSTAB t, sa_dependent_views('RBPcvViewF') v WHERE t.table_id = v.dep_view_id;Does it list RBPcvViewP?
Yes it does.