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.

Dig Deeper on DB2 UDB (universal databases)

Three ways SQL can count rows by type I want to get the individual Type field data and their count in SQL.

FAQ: Creating and altering SQL Server tables Whether you want to select rows, move records, track activity or perform various other table-related tasks, this collection of frequently asked questions offers expert insights to help.

SQL defined database: Enjoy the view Paul Tuohy provides a simple example to show you how SQL defined views may be of benefit in an existing environment. The easiest way to start using views is with iSeries Navigator, which is what I will be using in the following examples. I will also provide the corresponding SQL statements.

Comparing performance of mapping programs This top from SDN contributer, Udo Martens demonstrates how to compare the runtime performance and stability of six kinds of mapping programs in XI.

Script to select specific record from a multiple-records table Expert Greg Low explains how to select the correct records when they share an ID.

Easy column headings for query output files When using Query/400 to output a file the column headings can become less than meaningful. Use this technique to update the column headings.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

Meet all of our AS/400 experts

View all AS/400 questions and answers

Please create a username to comment.

Top 4 open source automation tools for admins

U.S. facility may have best data center PUE

Increase efficiency with data center temperature monitoring

Dig Deeper on DB2 UDB (universal databases)

Related Q&A from Kent Milligan

Have a question for an expert?

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.