Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Creating SQL views

Kent Milligan
By
Published: 20 May 2005
Is there a way to create a SQL view where fields are dynamically obtained each time the View is used. I am trying to do an analogy with the Logical Files ability to handle not mapped fields, and each time the Physical File is modified, the Logical one reflects the change without any DDS change for their structure.

For example:

Step 1 -- I created a table (testtable) with four fields (i.e. field1, field2, field3, field4)

Step 2 --I created two SQL views, using the following SQL statements:

CREATE VIEW library/testview1 AS SELECT field1,field2,field3,field4 FROM library/testtable;

CREATE VIEW library/testview2 AS SELECT * FROM library/testtable

Step 3 -- I added a field (i.e. field5) to the

Step 4 -- I ran a query SELECT from both views, and both of them gave me the same result, in this case only the four fields. When I ran the query for the testview2, I wanted to see the field5 in the given result, however, it appears the structure's view is taken solving the SELECT * statement and remains static forever.

As you can see, a SQL view doesn't have the same behavior as a native OS/400 Logical File, due to the LF offer the ability to reference fields in a static or dynamic way.

I assume that you are using ALTER TABLE to dynamically add a new column to the table. Once the view has been created (even if it has been created as you described), it cannot dynamically add new columns as new columns are added to the FROM table. This behavior follows ANS and ISO SQL standards. The standard dictates that adding a new column does not affect any of the views.

For the view to be redefined to pull in the new columns you would have to delete and recreate the view as follows:

DROP VIEW library/testview1;
CREATE VIEW library/testview2 AS SELECT * FROM library/testtable

However, you would not have to recompile any programs that use this view.

==================================
MORE INFORMATION ON THIS TOPIC
==================================

Visit the ITKnowledge Exchange and get answers to your DB2 questions fast.

Check out this Search400.com Featured Topic: Expert advice on DB2

Search400.com's targeted search engine: Get relevant information on DB2/400.

Dig Deeper on DB2 UDB (universal databases)

SearchDataCenter

Close