Now that you've made the tables and columns easier to understand, you thought you’d get a break. Well, that’s not how things work. The effect will be the opposite: users will have even more complex questions than before, as they want to explore the database but lack a map to do so.

You were hoping to get some “real work” done, now that you no longer have to decrypt table and column names all the time. Unfortunately, that’s not happening – not yet, at least. As you opened the metaphorical flood gates of the database, the users are now swimming in a sea of questions, because the way the data “works” within the database is not clear to them. This problem – having the data relations in the program code instead of the database code - is well-known to all IBM i developers. While I’m not going to address the issue now fully (it’ll be later in this series), let’s start with a quick fix.

Hiding Database Complexity

Now the users love their new explicitly named tables and are starting to build their own queries. But they’re still coming back to the IT staff with questions like “how do I link this table to that one?” or “Where can I find this information? I can find the header record, but where are the details?” and so on. This leaves you with two options: either build a database “map” (which I’ll discuss later) or hide the database complexity.

For the moment, let’s explore the second option. How are we going to do that? Well, the previous subseries introduced views and showed a big view that links all the tables in the UMADB database. It’s a cumbersome construct that probably won’t have any real-world use. Instead, let’s build a couple of smaller, more focused views. I’ll use the opportunity to introduce some Data Manipulation Language (DML) details you might not know about.

Using the Same Table Twice in a Query

Let’s start with a more or less simple view: Course Information. This view provides extended information about each course, listing the course and its staff (the course director and the teaching assistant). The problem resides in the fact that the Courses table has two links to the Teachers table: both the Course Director and the Teaching Assistant are teachers. To display their information, I could devise a complicated solution, filtering data and displaying two lines for each course, or I could use the same table twice in the same query.

This simple and elegant solution relies on the use of correlation names. Whenever you use a table on a SELECT statement, you can specify a correlation name (also known as an alias) to make the statement more readable. The same technique can be used to solve our little problem. Let’s follow the methodology I usually use to create views, which is described previously in this series:

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.

I already know that I’ll be using the Courses and Teachers tables, and I know I’ll link the Courses table twice to the Teachers table (one for the Director’s data and the other for the Teaching Assistant’s), so let’s skip directly to step 3. Here’s the SELECT statement:

SELECT      COURSES.COURSE_ID

      , COURSES.NAME

      , COURSES.DESCRIPTION

      , COURSES.DEP_NAME

      , DIRECTOR.NAME as "DIRECTOR_NAME"

      , DIRECTOR."RANK" as "DIRECTOR_RANK"

      , TA.NAME as "TA_NAME"

      , TA."RANK" as "TA_RANK"

FROM UMADB_CHP4.TBL_COURSES COURSES

      INNER JOIN UMADB_CHP4.TBL_TEACHERS DIRECTOR

ON COURSES.CODE = DIRECTOR.NAME

      INNER JOIN UMADB_CHP4.TBL_TEACHERS TA

ON COURSES.COTA = TA.NAME

;

Note that I’m using two aliases for my copies of the Teachers table in this statement. SQL will allow you to treat them as two different tables. By choosing appropriate aliases for these copies, I manage to keep some degree of readability on an otherwise messy and complicated query. If you run this query, you should get the expected output: details about the courses, coming directly from the Courses table, plus the ranks of both the course director and teaching assistant. This means that we’re ready to create the view, which is the statement above plus a pair of lines:

CREATE VIEW UMADB_CHP4.VIEW_COURSE_INFORMATION

   FOR SYSTEM NAME V_COURSE_1

   AS

SELECT      COURSES.COURSE_ID

            , COURSES.NAME

            , COURSES.DESCRIPTION

            , COURSES.DEP_NAME

            , DIRECTOR.NAME AS "DIRECTOR_NAME"

            , DIRECTOR."RANK" AS "DIRECTOR_RANK"

            , TA.NAME AS "TA_NAME"

            , TA."RANK" AS "TA_RANK"

FROM UMADB_CHP4.TBL_COURSES COURSES

            INNER JOIN UMADB_CHP4.TBL_TEACHERS DIRECTOR

ON COURSES.CODE = DIRECTOR.NAME

            INNER JOIN UMADB_CHP4.TBL_TEACHERS TA

ON COURSES.COTA = TA.NAME

;

While this is a good way to start and should allow for some practice, there’s more to be discussed. In the following article, I’ll extend this view to include information from other tables using sub-queries. Until then, you know the drill: your comments, corrections, and suggestions are most welcome – use 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