Views are an excellent way to hide the complexity of your data structure, especially if it’s a complicated one. The issue is that their underlying code quickly becomes complex and challenging to understand and maintain. Let’s see how this can be avoided or at least mitigated.

There’s no better way to help users find their way around your database than with a few well-constructed views, as they provide a neat way to show related information across multiple tables. However, this comes with a hidden cost: code complexity and future maintainability. Let’s explore one way to mitigate this.

Using Sub-queries

Now, let’s say I want to extend the information provided by this view to include the number of classes for each course. I need to link the Courses and Classes table by the course name, right? Let’s ignore the fact that the Classes table also includes a “class year” column that is part of its unique key (at the application level, because as far as the database is concerned, Classes’ primary key is its unique ID: column Class_Id). Unfortunately, this is a common situation: the table’s primary key is an identifier, but it’s not always used as such. In some situations, a combination of other columns (a non-primary key) is used to join tables. If the combination of values of these columns produces unique values, i.e., the other, non-primary keys are unique, everything should work. I can extend the SELECT statement I’ve been working with to include the Classes table, using another INNER JOIN, and add a COUNT to the SELECT clause, like this:

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"

      , COUNT(CLASSES.COURSE_NAME)

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

      INNER JOIN UMADB_CHP4.TBL_CLASSES CLASSES

            ON COURSES.NAME = CLASSES.COURSE_NAME

GROUP BY    COURSE_ID

, COURSES.NAME

, DESCRIPTION

, DEP_NAME

, DIRECTOR.NAME

, DIRECTOR."RANK"

, TA.NAME

, TA."RANK"

;

This works, no doubt. But it can get confusing, really fast: try adding a few more tables, and you’ll see what I mean. Instead, you can use a sub-query, which is nothing more than a query within a query. It can be used almost anywhere in an SQL statement and provides more flexibility and readability than the solution above. Let’s rewrite the statement above using a sub-query to replace that COUNT(CLASSES.COURSE_NAME) line and all the associated “baggage”:

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"

      , (

         SELECT   COUNT(COURSE_NAME)

         FROM     UMADB_CHP4.TBL_CLASSES CLASSES

         WHERE    CLASSES.COURSE_NAME = COURSES.NAME

         GROUP BY       COURSE_NAME

      )

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

;

This is a “cleaner” solution, but it comes with a cost: the database engine can’t always properly optimize a query with sub-queries. In other words, sub-queries tend to run slowly. Lateral joins (left/right inner/outer joins) are almost always faster than a sub-query. You need to experiment with the data and figure out whether performance is (or can be, in the future) a problem. Later, I’ll discuss how you can “see” what the database engine is doing, but for now, it’s essential to keep this in mind: using sub-queries in the SELECT clause is sometimes useful, but it should be the exception, not the rule. If you really need this type of functionality and the all-in-one query is not working for you, consider using a temporary table instead. It’s a two-step process, but it’s often a better solution.

There are other interesting uses for sub-queries, such as inline views, for instance. I’ll also use a sub-query on a different setting later in this subseries to achieve something totally different.

Let’s wrap this up with a few words of advice regarding views. From personal experience, I can tell you that what you think are the best candidates for views are not always what the users will need. Keep track of the users' most frequent requests, i.e., their more common or complex queries, and start building views to match those – within reason. You don’t need a view to hide every SELECT statement with a couple of JOINS.

You should also keep an eye on data quality, because the end users will come to you with what seems like a database structural issue that is, in fact, a data quality issue. We will discuss the EXISTS predicate in the next article, which can help in this scenario.

Until then, explore the UMADB database and build some views for practice. Start small and expand over multiple iterations, always taking the time to test the results.

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