Creating SQL views

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.

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please login.

You have exceeded the maximum character limit.

Please provide a Corporate E-mail Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

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.

SQL Server Data Tools 2012 makes developing DB projects easier

SQL Server Power View makes BI reporting look simple

Making the most of RPG data handling on IBM i

Organize metadata in Microsoft SharePoint Server 2007 by content type

Please create a username to comment.

Learn the main Linux OS components

New Dell EMC PowerEdge servers are built for the rugged edge

Initiative aims to improve data center incident reporting

Dig Deeper on DB2 UDB (universal databases)

SQL Server Data Tools 2012 makes developing DB projects easier

SQL Server Power View makes BI reporting look simple

Making the most of RPG data handling on IBM i

Organize metadata in Microsoft SharePoint Server 2007 by content type

Related Q&A from Kent Milligan

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.