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.

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 a couple of options: you can either build a database “map” (which I’ll discuss later), or you can hide the database complexity.

Editor's Note: This article is excerpted from chapter 4 of SQL for IBM i: A Database Modernization Guide, by Rafael Victória-Pereira.

For the moment, let’s explore the second option. How are we going to do that? Well, the previous chapter introduced views and actually 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 (course director and 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 in a SELECT statement, you can specify a correlation name (also known as an alias) in order 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 in Chapter 3:

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

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:

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 basically the statement above plus a pair of lines:

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). 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:

This works, no doubt. But it can get really confusing, really fast: just 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”:

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 join (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 important 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 in a different setting later in this chapter to achieve something totally different.

Using EXISTS to Omit Records on a SELECT Statement

Another typical request from users is to show only records from a table that meet some condition, which doesn’t depend directly on that table. In other words, something that you can’t easily put in the WHERE clause. Here’s an example: imagine that I want to refine the Course Information view to hide the courses that don’t offer classes at the moment (or some other convoluted restriction).

You can do this with OUTER JOINs or really complex statements, but there’s a (probably) simpler solution: just use the EXISTS predicate. Let me show you an example: let’s take the SELECT statement from the Course Information view and “hide” courses without classes. In other words, only the course names that have at least one match in the Classes table will be shown. Seems complex? It’s actually quite simple:

I added a WHERE clause, with an EXISTS predicate and a query inside it. Note that this is not a sub-query, in the traditional sense: I’m not interested in how many rows or columns are returned by this inner query. I just need to know if the number of rows returned is zero or not, for the specified condition (in this case, CLASSES.COURSE_NAME = COURSES

.NAME). If at least one record is returned by the inner query, then the condition with the EXISTS predicate will evaluate to TRUE, meaning that the Courses table record with that course name will be returned. Otherwise, if there aren’t any classes with that course name (i.e., the inner query doesn’t return a single record), the condition evaluates to FALSE and the Courses record is ignored. This is a simple way to restrict which records are returned by a query based on complex conditions. You can also use NOT EXISTS to make the conditions more readable and maintainable.

Let’s create the conditions necessary to see this in action. As things stand, all courses have classes associated with them. In the downloadable source code, you’ll find data about three courses, each with four classes. You can confirm this by running the SELECT statement with the EXISTS predicate I’ve just shown. Now let’s add an additional course record and run the SELECT again to see what happens. If you’re following along, type the following statement:

If you run the SELECT statement again, you’ll still see the same three courses, even though you’ve just added a fourth to the Courses table. This is because there’s no match for “Philosophy Studies and Shakespeare” in the Classes table. Now let’s create a class for this course:

Finally, run the SELECT statement once again. Now there are four courses, as many as exist in the Courses table, because we just added a Class record to match the “Philosophy and Shakespeare” course.

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