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 ?

asked 28 Aug '19, 09:27

Ben8sens's gravatar image

Ben8sens
166121320
accept rate: 44%

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...

(28 Aug '19, 09:50) Volker Barth

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.

(28 Aug '19, 10:07) Ben8sens
Replies hidden

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?

(28 Aug '19, 10:27) Volker Barth

Yes it does.

(28 Aug '19, 11:48) Ben8sens
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • 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:

×31

question asked: 28 Aug '19, 09:27

question was seen: 780 times

last updated: 28 Aug '19, 11:48