Creating SQL views
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.
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.