Editor's note: This article is an excerpt from Mastering IBM i, published by MC Press.

Written by James Buck and Jerry Fottral

SQL's version of a logical file is a view. Although a view is conceptually similar to a logical file, SQL's greater flexibility and ability to use functions and expressions make views far more powerful than logical files. For example, in a logical file there is no way to select group summary records based on the value returned by a column function, as we did in the final example of the previous article. Nevertheless, in SQL, creating such a view is easy; in fact, we already have most of the code for it. The only thing we need to do is prefix our earlier Select statement with a Create View statement and then name the virtual column created by the Min function. Here is the statement to create the view of department elders:

Online Analytical Processing (OLAP) adds a variety of processing features to SQL syntax that makes it much more useful as an ad hoc analysis tool, and this article introduces the first of those functions.

Written by Joe Pluta

SQL has always been a great tool for querying data. The problem is that SQL doesn't always lend itself to the sort of pattern analysis that's needed in enterprise data processing. Something goes awry in the database and the only way to identify the source of the problem is to backtrack through history. Let me give you a real-world example (the data has been changed to protect the innocent, but the situation is pretty common).

Editor's note: This article is an excerpt from Mastering IBM i, published by MC Press.

Written by Jim Buck and Jerry Fottral

If you tell SQL to retrieve rows that have the same column values, it will do that, duplicating those rows in the result table. For example, if you wanted to list the employee numbers of any employees assigned to any project, you might simply type...

Simplify your SQL SELECT statements, provide an interface to pass parameters to create dynamic tables, automatically log access to tables, and create a single point of maintenance for the SQL code you use repeatedly.

Written by Jim Staton

In my previous article, "Reuse Your RPG Code with SQL User-Defined Functions," I discussed how you could reuse RPG business logic in SQL by taking an RPG program or service program that returns a single value and then using the SQL CREATE FUNCTION command to make the business logic available to any high-level language (HLL), such as PHP or Java, or any tool that uses SQL to access data on your system. There are times, however, when you might want a function that returns not just a single value but a set of values or even a table. In that case, you could consider using a User-Defined Table Function (UDTF).

The recently announced Technology Refresh #5 for the IBM i 7.1 release includes an enhancement that makes management of SQL stored procedure and function objects easier.

Written by Kent Milligan

SQL procedures and functions have been supported by DB2 for i for many releases. This support enables developers to create procedural SQL objects like the example procedure shown in Figure 1. When the SQL Create Procedure statement in Figure 1 is executed, DB2 for i generates a C program object into the specified library (TESTLIB). The generated C program object gets called whenever an application invokes the TestProc stored procedure.

Editor's Note: This article is an excerpt from Mastering IBM i (MC Press, 2011).

Written by Jim Buck and Jerry Fottral

In the first article of this series, we began to examine the basic form of an SQL statement to obtain information from a database table or tables:

This article is an excerpt from Mastering IBM i (MC Press, 2011).

Written by Jim Buck and Jerry Fottral

Structured Query Language (SQL) has become the mainstay for data manipulation on most platforms today. It is important for today's IBM i professional to understand the traditional DDS tools, but most new database systems are created and manipulated using SQL. The intent of this series of articles is to expose the reader to both the traditional tools and the latest tools and concepts. SQL is a complete database language with data-definition, data-manipulation, and data-control components.

Use the WITH keyword to make your SQL statements easier to code and maintain.

Written by Sam Lennon

Common table expressions (CTEs) allow you to break down complex SQL logic into smaller pieces. This makes development and testing easier, and when you come back to the code next year you'll understand it more quickly. That can't be bad, and a CTE is not hard to use. In this article, I'll provide working examples of CTEs that demonstrate their ease of use and the clarity they can provide.