Views are windows to the tables. We’ll talk about SQL views, in this and the next few articles. Keep reading to find out more!

A First Step Toward Easier Data Access: Creating a Few Simple Views

Now that I have (and I hope you have, too, assuming you read the previous article of this series) created all the tables on the UMADB_CHP3 schema, including their primary keys, it’s time to simplify data access. However, although I’ve created primary keys based on unique IDs for all the tables, this doesn’t accurately represent the links between the tables that are kept by the application. For instance, the Classes table’s real key is composed of the student name, class name, and class year. For now, I’ll keep the keys as they are and use those columns to establish the necessary links. I’ll return to this issue later and replace these faux primary keys with the real ones. First, let’s take small step toward easier data access by creating a few simple views.

Why Do You Need Views?

Typically, views are used to hide database complexity and/or protect sensitive data. Let’s start with the database-complexity issue: Even in our simple database, it’s not very practical to include the same INNER JOIN lines on every single SELECT statement to establish the appropriate connections between tables. The pragmatic response is to create a view that joins the tables (much like a joined logical file) and use it in the SELECT statements, instead of the direct access to the tables.

This is very practical, but it doesn’t offer much security. But note that by using the view instead of the table(s), you’re accessing a single, separate object. Because you can specify different authorities for different objects, you can actually hide a table behind a view by changing the table object authorities in order to prevent access to it (with a PUBLIC *EXCLUDE, for instance) and granting limited access to the view itself. This way, you can ensure that only the columns mentioned explicitly in the view are accessible. For instance, I can create a view over the Teachers table that doesn’t include the teacher salary column. If I combine that with a change in PFTEM’s authorities, I can prevent whoever needs to use the teachers’ other data from seeing the teachers’ salaries.

How Do You Create Views?

Having said that, let’s go through my usual process for creating a view:

1. Identify the tables and columns for the view.
2. Determine how the tables are linked.
3. Write a SELECT statement with the appropriate tables, columns, and JOINs.
4. Test, adjust, test, adjust...until you get it right.
5. Finally, create the view using the SELECT statement, fine-tuned by the tests.

Let’s start with a simple example that links the Students, Courses, and Classes tables. This view doesn’t include every single column of these three tables, only a select few. Let’s include the student, class, and course names plus the course description. Naturally, I only want students who are enrolled in classes—that way I can simplify the joins and use only INNER JOINs. So let’s follow the steps I mentioned and get started!

I’ve already identified the tables: I’ll need the Students, Classes, and Courses tables. I already know how to link them (in case you missed it, it’s covered here and here). I’m now ready to write the appropriate SELECT statement:

SELECT        STNM AS "Student_Name"

              , CLNM AS "Class_Name"

              , CONM AS "Course_Name"

              , CODS AS "Course_Description"

   FROM       UMADB_CHP3.PFSTM ST

   INNER JOIN UMADB_CHP3.PFCLM CL ON ST.STNM = CL.CLSN

   INNER JOIN UMADB_CHP3.PFCOM CO ON CL.CLCN = CO.CONM

;

Note that I’m not using the newly defined primary keys. This is because the Classes and Courses tables are not fully modified to use the new primary keys. We’ll discuss those modifications later. If you run this statement, you should get the expected results and even nice column headers. Because this is a simple SELECT, there’s no actual need for testing and adjusting the statement, so let’s create the view by adding the SELECT statement to a CREATE VIEW statement:

CREATE VIEW  UMADB_CHP3.View_Students_Classes_1

AS

SELECT        STNM AS "Student_Name"

              , CLNM AS "Class_Name"

              , CONM AS "Course_Name"

              , CODS AS "Course_Description"

   FROM       UMADB_CHP3.PFSTM ST

   INNER JOIN UMADB_CHP3.PFCLM CL ON ST.STNM = CL.CLSN

   INNER JOIN UMADB_CHP3.PFCOM CO ON CL.CLCN = CO.CONM

;

It’s important to follow along, either by typing or copy-pasting from the downloadable source code, because of what I’ll show next. To check whether the view is properly created, let’s run a simple SELECT over it:

SELECT        *

FROM   UMADB_CHP3.View_Students_Classes_1

;

Again, the result is what’s expected: an exact match of the previous SELECT’s result. Now let’s include a WHERE clause to list all the classes of a particular student:

SELECT        *

FROM   UMADB_CHP3.View_Students_Classes_1

WHERE STNM = 'Anthony, Mark'

;

Oops! The statement ends in error, complaining it doesn’t know STNM. But it’s right there on the SELECT statement...or isn’t it? What’s happening is that my effort to embellish my view with nice headers did a bit more than I expected: in a regular SELECT statement, the column header (that bit following the <column name> AS part) is just that: a column header. However, when you use it in a CREATE VIEW statement such as the one I wrote here, it becomes more than that. It turns into the actual column name! So the correct SELECT statement over the view becomes this:

SELECT        *

FROM   UMADB_CHP3.View_Students_Classes_1

WHERE "Student_Name" = 'Anthony, Mark'

;

While not necessarily a bad thing, using this type of naming is dangerous because it’s case-sensitive. Try replacing "Student_Name" with "Student_name", and run the statement again. It will fail with the same error as it did when I ran it with STNM: it complains that column doesn’t exist on the specified tables (in this case, our newly created view). I’ll discuss the topic of user-friendly names in greater depth later in this series, but let’s fix this view so that both ugly four-letter cryptic names and human-readable longer names work.

The CREATE VIEW statement used before was basically the SELECT statement plus a line that gave the view its name. However, just as you can specify the names of the columns on an INSERT statement, you can also do the same in this case. However, in order to keep both short and long names, I need to introduce an additional reserved expression: FOR COLUMN. This allows you to provide a system-friendly (and by that I mean a short 10-letters-tops name) to an SQL column. I know this may sound confusing, so let’s examine the new and improved CREATE VIEW statement:

CREATE VIEW  UMADB_CHP3.View_Students_Classes_1

   (

              "Student_Name" FOR COLUMN STNM

              , "Class_Name" FOR COLUMN CLNM

              , "Course_Name" FOR COLUMN CONM

              , "Course_Description" FOR COLUMN CODS

   )

AS

SELECT        STNM

              , CLNM

              , CONM

              , CODS

   FROM       UMADB_CHP3.PFSTM ST

   INNER JOIN UMADB_CHP3.PFCLM CL ON ST.STNM = CL.CLSN

   INNER JOIN UMADB_CHP3.PFCOM CO ON CL.CLCN = CO.CONM

;

The SELECT part is exactly the same, but the CREATE VIEW part is now much longer, and it resembles the first part of an INSERT statement, with a list of columns. However, note that after each column name there’s a FOR COLUMN followed by a shorter name, which you’ll recognize as the names given to the columns on the original tables. Before running this statement, you need to destroy the existing view, using a DROP statement:

DROP VIEW    UMADB_CHP3.View_Students_Classes_1;

You’re now ready to create the new version of the view. Let’s run the statement and then try a couple of different SELECT statements. The first one is the original SELECT, which failed because of the column STNM’s name:

SELECT        *

FROM   UMADB_CHP3.View_Students_Classes_1

WHERE STNM = 'Anthony, Mark'

;

This now works, because of the FOR COLUMN clause that identifies the "Student_Name" column as a system-friendly column named STNM. Now let’s try to run the same statement with the longer column name:

SELECT        *

FROM   UMADB_CHP3.View_Students_Classes_1

WHERE "Student_Name" = 'Anthony, Mark'

;

As expected, this also works. The FOR COLUMN is a very nice tool to have in your arsenal, as I’ll explain in greater depth later.

And that’s all for now. In the next article, we’ll focus on a different aspect of an SQL view: its similarities and differences when compared to a logical file. Until then, feel free to comment, correct, or suggest whatever you see fit using the Comments section below.

Rafael Victória-Pereira has more than 20 years of IBM i experience as a programmer, analyst, and manager. Over that period, he has been an active voice in the IBM i community, encouraging and helping programmers transition to ILE and free-format RPG. Rafael has written more than 100 technical articles about topics ranging from interfaces (the topic for his first book, Flexible Input, Dazzling Output with IBM i) to modern RPG and SQL in his popular RPG Academy and SQL 101 series on mcpressonline.com and in his books Evolve Your RPG Coding and SQL for IBM i: A Database Modernization Guide. Rafael writes in an easy-to-read, practical style that is highly popular with his audience of IBM technology professionals.

Rafael is the Deputy IT Director - Infrastructures and Services at the Luis Simões Group in Portugal. His areas of expertise include programming in the IBM i native languages (RPG, CL, and DB2 SQL) and in "modern" programming languages, such as Java, C#, and Python, as well as project management and consultancy.

---

MC Press books written by Rafael Victória-Pereira available now on the MC Press Bookstore.

		Evolve Your RPG Coding: Move from OPM to ILE...and Beyond Transition to modern RPG programming with this step-by-step guide through ILE and free-format RPG, SQL, and modernization techniques. List Price $79.95 Now On Sale
		Flexible Input, Dazzling Output with IBM i Uncover easier, more flexible ways to get data into your system, plus some methods for exporting and presenting the vital business data it contains. List Price $79.95 Now On Sale
		SQL for IBM i: A Database Modernization Guide Learn how to use SQL’s capabilities to modernize and enhance your IBM i database. List Price $79.95 Now On Sale

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions