Format and name derived columns when you use SQL to do ad hoc analysis of information in an SQL table.

By Skip Marchesani

I frequently use Interactive SQL or Run SQL Scripts in Navigator to do ad hoc analysis of information in an SQL table, which often results in one or more new columns being derived or created based on existing columns. When I first began doing these types of queries, I was frustrated with the way SQL identified and formatted a derived column in the result set. 

Learn how to use host variables to get the most from your embedded SQL statements.

by Adam Glauser

In its simplest form, embedded SQL in RPGLE is not much different from the type of SQL you can run using the RUNSQLSTM command. You can use DDL to create and drop tables; you can even insert and delete rows. However, you will soon find that you will want to have the SQL statement affect different rows, depending on user input or other criteria. That's where host variables can help.

Take the hassle out of managing procedures and functions with some easy-to-use tools.

By Scott Forstie

Procedures and functions work as desired only when they're kept in sync with the QSYS2/SYSROUTINE and QSYS2/SYSPARMS catalogs. For some users, maintaining the executable code and catalog entries over many machines can seem about as difficult as Dorothy finding her way out of Oz and back to Kansas. The goal of this article is to pull back the curtain and expose some of secrets known only by the Wizard.

Execute a script that contains a single SQL statement, multiple SQL statements, or even CL commands!

By Skip Marchesani

Several years ago, I began using what is now called the Run SQL Scripts function (also known as the SQL Script Center or Script Center in iSeries Navigator or System i Navigator, respectively) to demonstrate various SQL functions and to answer SQL syntax and function questions when I teach SQL-related topics. Now that the SQL Script Center is becoming more widely known and used, when I teach and use it for demonstrations, I am getting more and more questions about how it works.

Are you aware that a single SQL statement can process two or more tables without the tables being joined together?

By Skip Marchesani

Subselect is the capability to embed up to 256 SQL SELECT statements inside a SELECT, UPDATE, INSERT, DELETE, or CREATE TABLE statement without having to do a join of the tables involved, which significantly enhances the power and function of these SQL statements