SQL without SQL and more!

What You'll Learn In This Article

1) Even if you don't have SQL/400, you can still run every SQL statement allowed in SQL/400!

2) Even if you have SQL/400, you can use Query Management instead when you need to submit SQL to batch.

3) Even if you have Query/400, you can use Query Management as a front-end to gain more powerful SQL selection capabilities.

In April 1991, we ran an article by the author covering this same subject. Since then, he has learned that Query Management is more powerful than he previously thought. Although this article covers some of the same material, many new concepts are presented.

Would you like to be able to run SQL on your AS/400, but you don't have SQL/400? Do you have Query/400, but wish you could do more powerful selections? For example, selections including parenthetical expressions, unions and subselects. Maybe you have SQL/400 and you are able to do the complex selections, but find that its report formatting capabilities are lacking. Would you like to be able to submit SQL/400 to batch? Query Management is the answer to each of these situations.

Query Management is a superset of both SQL/400 and Query/400. It is included free on every AS/400 regardless of whether or not you have either SQL/400 or Query/400 installed. SQL is a powerful relational database manipulation system (see series on SQL by John Gaughan in Midrange Computing, April - June 1991) which provides database update, delete, insert, display and list capabilities. SQL/400 is IBM's implementation of SQL on the AS/400. SQL/400 doesn't have display and report formatting capabilities; SQL/400 lists will display the retrieved information in a very basic format--defined headings, headers, footers or totals are not available. But then, that's what AS/400 Query is for. Query/400 is a user friendly report generator that allows the inner and outer join of up to 10 database files. Like SQL/400, albeit with less power, Query/400 has record selection, calculated fields and record sequencing.

Query Management has all the capabilities of Query/400 and SQL/400 put together into one package. You can overcome the shortcomings of either while incorporating the best of both in one query. You can put the power of SQL/400's advanced selection capability together with the reporting features of Query/400. For example, you could use Query Management to perform a formatted report of a selection which utilized unions and sub-selects. Query/400 could not do this--its selection is limited to equijoins. In fact, although we won't be covering it in this article, you can even improve upon Query/400's reporting.

Query Management is made up of two functions:

1) The ability to enter any valid SQL statement into a source file member, compile it with the Create Query Management Query (CRTQMQRY) command into the executable AS/400 object type *QMQRY, and then run it with Start Query Management Query (STRQMQRY).

2) The capability to create sophisticated report and display formats for use with this compiled Query Management SQL statement. This is accomplished by encoding Query Management form control statements to a source file member and compiling it into object type *QMFORM with the create Query Management Form (CRTQMFORM) command.

I'll be covering only the first function in this article. The second is a very complex subject; if you're interested in it you should refer to Query Management/400 Programmer's Guide (SC41-8192) and Query Management/400 Reference (SC41-8193) for more information.

SQL Without SQL/400

The ability to compile SQL statements in a source file with CRTQMQRY is even more powerful than I had first understood when I put together my initial article on the subject. Any SQL statement can be compiled, not just the SELECT statements for creating displays and reports. This should really excite those shops that don't have SQL/400. You won't be able to use interactive SQL or embed SQL statements into your HLL as you would with SQL/400, but you will have full functionality of single SQL statements. With Query Management, quick-and-dirty reports and database clean up programming can be replaced with faster and more direct SQL statements.

Consider, for instance, a manufacturer's decision to change an item's description. To limit redundancy, the item descriptive information for a part is kept in the item master file (ITMSTR). The following SQL statements are not limited to SQL/400 and can be typed into a source file, compiled with CRTQMQRY and executed with STRQMQRY.

 UPDATE ITMSTR SET ITMDES = 'Superwinch' WHERE ITMDES = 'Winch' 

The steps involved would be:

1) Create a member in source file QQMQRYSRC named UPDITMDSC that contains the above statement.

2) Create the *QMQRY object:

 CRTQMQRY QMQRY+ (*CURLIB/UPDITMDSC) + SRCFILE(*LIBL/QQMQRYSRC) + SRCMBR(*QMQRY) 

3) Run the SQL statement:

 STRQMQRY QMQRY+ (*CURLIB/UPDITDSC) + QMFORM(*SYSDFT) + NAMING(*SYS) + ALWQRYDFN(*NO) 

4) Say WOW! I just performed SQL without having SQL/400!

Keep in mind that you can only include one SQL statement in a *QMQRY object.

Without SQL/400, you don't have the availability of interactive SQL/400. But we can fake it a bit. Enter this simple SQL statement into a source member and create it with CRTQMQRY, naming it say, ISQLSEL. Make sure it's entered all in uppercase letters.

 SELECT &FIELDS FROM &FILES WHERE &WHERE 

You can then interactively run an SQL statement from the command entry screen with:

 STRQMQRY QMQRY(ISQLSEL) + SETVAR((FIELDS 'field1, + field2, field3 ...') (FILES 'file1, + file2, file3 ....') (WHERE 'field1 >= + this and ...')) 

Voila! Interactive SQL. The variables FIELDS, FILES and WHERE can be filled with any valid SQL syntax. If you're working from the command entry screen, be sure to include detail messages on the command entry screen (command-10); SQL error messages will then be displayed, such as:

 Column FIELD99 not in specified tables. RUN QUERY command ended due to ERROR. STRQMQRY command failed. 

See the accompanying sidebar for the ultimate comfort in executing any SQL statement without SQL.

You can also use this method for testing an SQL statement that you will use again later. Once you have developed and tested your SQL statement, reenter it into a Query Management source file, create it and retest the statement for typographical errors. If you want the records from a SELECT query to be placed in a file after the Query Management Query is developed, specify the STRQMQRY command's output option to be a database file:

 STRQMQRY QMQRY(yourqry) + OUTPUT(*OUTFILE) + OUTFILE(lib/workfile) 

The database file output from the STRQMQRY command must, however, be in an SQL Collection. An SQL Collection is simply an AS/400 library that was created with the SQL CREATE COLLECTION statement. This library will then contain a variety of objects pertinent to SQL applications such as database management table files, journals and journal receivers.

Now wait a minute, you ask, how can shops without the benefit of SQL/400 create an SQL Collection? Simple. Since we can run any SQL statement, just add a source member to your QM source file and enter one line:

CREATE COLLECTION newlibnam

Then create and run the Query Management Query with CRTQMQRY and STRQMQRY. This process will create the required SQL library--I told you that you could use any SQL statement in a QM Query. Submit the create collection QM Query to batch since it will run for a while as it creates all the SQL objects.

You can create QM objects with variables (&variable) for each SQL statement just like the two previous examples.

Useful Even If You Have SQL

Even shops with SQL/400 can benefit from Query Management. To submit an SQL/400 statement to batch, to use an interactively developed SQL/400 statement in an application or to pass variables to an SQL/400 statement, the SQL/400 statement must be embedded within a HLL. Because Query Management uses compiled SQL statements and supports variables, it can replace lengthy high- level programs that must accept passed parameters, perform embedded SQL and then format reports.

SQL/400 has the additional capability of executing dynamic SQL/400 statements. The use of dynamic SQL/400, however, also requires the complex embedding of SQL statements within a HLL program. Application demands for dynamic database retrievals can be resolved with Query Management queries that utilize passed variables. Query Management variable substitution can occur anywhere in a Query Management SQL statement, allowing unlimited variations of SQL database access. For example, the following Query Management SQL statement containing an embedded variable could be compiled. We'll call it the Query Management object DYNSQL:

 SELECT DISTINCT &FIELD FROM &LIB/&FILE ORDER BY 1 

The following Start Query Management Query command would then execute the above SQL statement:

 STRQMQRY QMQRY(DYNSQL) + SETVAR((FIELD empnam) + (LIB yourlib) + (FILE emplmast)) 

This execution would list each employee's name in the employee master file in name sequence. But then

 STRQMQRY SETVAR((FIELD '*') + (FILE 'emplmast + where mthsal * 12 > + 25000')) 

would list all the fields in the employee master file that have a yearly salary greater than $25,000. The list would be sequenced, in this case, by whatever happened to be the first field name in the file. This strange capability to pass any SQL string to a compiled QM Query with embedded variables allows limitless ad hoc list, update and delete capabilities. Commands can also easily be created to prompt users for the variable parameters to the SQL statements such as data fields to select, sort fields or selection ranges.

You can use interactive SQL/400 to interactively develop and test a Query Management SQL statement that retrieves the appropriate information from your database. Once the SQL statement is correct, it needs to be executed in order to be saved. After execution, you can save the session by using F13 (Work With Session Service). Use Option 4 (save session in a source file). Save the session to a member name of your choice in source file QQMQRYSRC. You can also save this session from the Exit Interactive SQL screen.

The new Query Management source file member created from the interactive SQL session can then be edited with SEU to remove all but the proper SQL statement from the session. There will be extraneous statements inserted by Interactive SQL such as "SELECT statement run complete" that you'll need to delete. During this edit process, variables and comments can be inserted as required: variables are defined similarly to CL variables, with an ampersand; and comments are delimited with pairs of double dashes. The resulting Query Management SQL source statement will then be ready for compilation.

Once the Query Management Query is compiled, it can be executed.

Improve Query/400 Selection With Query Management

Query/400 has a decent selection capability, but it is nowhere near as powerful as SQL is. What's missing is the ability to include parenthetical expressions in selections, unions and subselects. Let's take a look at the following example of a parenthetical expression's usefulness. Without it, you would enter:

 ITEM = 'A' AND STATE ='CA' OR ITEM ='B' AND STATE ='CA' 

With parenthetical expressions:

 (ITEM = 'A' OR ITEM = 'B') AND STATE = 'CA' 

Although a simplified example, you can see how useful this ability is.

To add all the features of SQL/400 to your Query/400 query, you need to reverse engineer a created Query. Two commands, Retrieve Query Management Query (RTVQMQRY) and Retrieve Query Management Form (RTVQMFORM) will extract the two parts of a Query/400 query--the QMQRY (the SQL selection statement) and the QMFORM (report format)--into source members. You next modify the QMQRY source member, create new objects from both the QMQRY and QMFORM source member, and then run STRQMQRY against them. (Again, we won't be discussing the modification of the QMFORM source member in this article.) The following simple instructions add the power of SQL to your QUERY/400 queries.

Once a Query/400 query has been created, retrieve the QMQRY SQL statement into a source member with the RTVQMQRY command:

 RTVQMQRY QMQRY(qry400nam) + SRCFILE(yourlib/+ QQMQRYSRC) + SCRMBR(newqmqry) + ALWQRYDFN(*YES) 

Now you have the SQL statement in the QMQRY source member that you can modify to your heart's content. When satisfied with the changes, you can create the QMQRY object from the source member just as you did when you created a QMQRY source member from scratch:

 CRTQMQRY QMQRY(newqmform) + SRCFILE(yourlib/QQMQRYSRC) + SRCMBR(*QMQRY) 

Now you must translate the Query/400 report format into a QMFORM source member specification with RTVQMFORM):

 RTVQMFORM QMFORM(qry400nam) + SRCFILE(yourlib/+ QQMFORMSRC) + SRCMBR(newqmform) + ALWQRYDFN(*YES) 

This will create a new QMFORM source member from the Query/400 object you created. Do not modify this member. The QMFORM source then requires compilation with the Create Query Management Form (CRTQMFORM) command:

 CRTQMFORM QMFORM(newqmform) + SRCFILE(yourlib/+ QQMFORMSRC) 

Now you can run your Query Management query, using the format created by Query/400 by substituting the *SYSDFT in the QMFORM parameter with your source member name:

 STRQMQRY QMQRY(yourqmsql) + QMFORM(yourqmform) 

Complications?

For those of you with a good understanding of SQL/400 and Query/400, be aware of the following complications. Some Query/400 functions such as selecting file members, record formats, collating sequence and edit words are not supported with SQL/400 or Query Management. Members, however, can be overridden with the override database file command OVRDBF. Query/400 also has an outer-join capability that can be mimicked in SQL with a NOT EXISTS subselect. Query/400's join with defaults can be emulated with two SQL SELECTs combined with an SQL UNION; the first select contains an equi-join and the second select contains default values with a NOT EXISTS subselect for the missing secondary record. Also, all QM form edits are not the familiar edit codes used with Query/400 and RPG--they are the new SAA edit types, including several character edit codes. Once the query is defined, it can be converted to a Query Management form source with the RTVQMFORM command and compiled with CRTQMFORM. There are a variety of additional report formatting capabilities available by directly coding or modifying Query Management form source.

Use It

The Query/400 and interactive SQL/400 front ends to Query Management relieve you of the tedious tasks of coding complex Query Management SQL statements and cryptic form source. Whether or not your company has SQL/400, both you and your company can benefit from using Query Management; ad hoc reports, one-time file cleanups and system prototypes are but a few of the immediate uses of Query Management. Remember the strengths of Query Management: the inherent power of complex SQL statements that Query/400 cannot replicate, report formatting unavailable with SQL/400 and superior to Query/400 and external variable support unavailable with either SQL/400 or Query/400.

Sidebar: The Run SQL Statement Command

by Ernie Malaga

Deck: Read on to learn how you can achieve the ultimate in comfort and convenience when you wish to run SQL statements without having SQL.

The Run SQL Statement (RUNSQLSTM) command allows you to run a single SQL statement without having SQL/400 installed on your system. RUNSQLSTM can be run interactively, submitted to batch, or included in a CL program.

For example, you can update the credit status code to 'H' (hold) to all customers having an open balance of more than $5,000 in past due invoices over 120 days old, as follows:

 RUNSQLSTM STM('update custmast set crdsts = + ''H'' where ovr120 > 5000') 

Simply enter the code for command RUNSQLSTM, CL program SQL001CL and QM Queries SQL001QQ and SQL001QQA, which are listed in Figures 2 to 5, and compile them as indicated at the bottom of each figure.

The RUNSQLSTM command has four parameters:

STM (statement) where you can enter the SQL statement you wish to run.

OUTPUT (output) which indicates where to send the output. It defaults to * (display if run interactively, printer if run in batch), but it can also have the values *PRINT or *OUTFILE.

OUTFILE (outfile) and OUTMBR (output member) are valid only if OUTPUT(*OUTFILE) is specified. OUTFILE names the database file that will contain the output of the SQL statement. OUTMBR contains two elements: the name of the member within the outfile, and an option to add or replace records in the member.

Note: If you request OUTPUT(*OUTFILE), RUNSQLSTM creates a SQL collection (library) named RUNSQLSTM@ as an intermediate repository for the outfile. You can delete this collection when you're done running RUNSQLSTM, but you should consider leaving it on your system at all times because its creation takes a long time.

Free SQL and Improved Query Through Query Manageme

Figure 1 Using RUNSQLSTM interactively

 Figure 1: Using RUNSQLSTM Interactively Run SQL Statement (RUNSQLSTM) Type choices, press Enter. SQL statement . . . . . . . . . __________________________________________ ______________________________________________________________________________ ______________________________________________________________________________ ______________________________________________________________________________ ______________________________________________________________________________ ______________________________________________________________________________ ______________________________________________________________________________ Output . . . . . . . . . . . . . * *, *PRINT, *OUTFILE F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display F24=More keys 
 
 Free SQL and Improved Query Through Query Manageme
 
 Figure 2 Command RUNSQLSTM
 
 RUNSQLSTM: CMD PROMPT('Run SQL Statement') PARM KWD(STM) TYPE(*CHAR) LEN(1430) MIN(1) + EXPR(*YES) PROMPT('SQL statement') PARM KWD(OUTPUT) TYPE(*CHAR) LEN(8) RSTD(*YES) + DFT(*) VALUES(* *PRINT *OUTFILE) + EXPR(*YES) PROMPT('Output') PARM KWD(OUTFILE) TYPE(Q1) PMTCTL(PC1) + PROMPT('File to receive output') PARM KWD(OUTMBR) TYPE(E1) PMTCTL(PC1) + PROMPT('Output member options') PC1: PMTCTL CTL(OUTPUT) COND((*EQ *OUTFILE)) Q1: QUAL TYPE(*NAME) LEN(10) DFT(OUTSQL) EXPR(*YES) QUAL TYPE(*NAME) LEN(10) DFT(QTEMP) + SPCVAL((*LIBL) (*CURLIB)) EXPR(*YES) + PROMPT('Library') E1: ELEM TYPE(*NAME) LEN(10) DFT(*FIRST) + SPCVAL((*FIRST)) EXPR(*YES) PROMPT('Member') ELEM TYPE(*CHAR) LEN(8) RSTD(*YES) DFT(*REPLACE) + VALUES(*REPLACE *ADD) EXPR(*YES) + PROMPT('Replace or add records') 
 
 Free SQL and Improved Query Through Query Manageme
 
 Figure 3 CL program SQL001CL
 
 SQL001CL: + PGM PARM(&STM &OUTPUT &OUTFILE &OUTMBR) DCL VAR(&FILE) TYPE(*CHAR) LEN(10) DCL VAR(&LIB) TYPE(*CHAR) LEN(10) DCL VAR(&MBR) TYPE(*CHAR) LEN(10) DCL VAR(&MBROPT) TYPE(*CHAR) LEN(8) DCL VAR(&OUTFILE) TYPE(*CHAR) LEN(20) DCL VAR(&OUTMBR) TYPE(*CHAR) LEN(20) DCL VAR(&OUTPUT) TYPE(*CHAR) LEN(8) DCL VAR(&STM) TYPE(*CHAR) LEN(1430) DCL VAR(&OFFSET) TYPE(*DEC) LEN(5 0) VALUE(0) DCL VAR(&REMAINS) TYPE(*DEC) LEN(5 0) VALUE(0) /* Validate input */ IF COND(&OUTPUT *EQ '*OUTFILE') THEN(DO) CHGVAR VAR(&FILE) VALUE(%SST(&OUTFILE 1 10)) CHGVAR VAR(&LIB) VALUE(%SST(&OUTFILE 11 10)) CHGVAR VAR(&MBR) VALUE(%SST(&OUTMBR 3 10)) CHGVAR VAR(&MBROPT) VALUE(%SST(&OUTMBR 13 8)) IF COND(&LIB *NE '*CURLIB' *AND &LIB *NE '*LIBL') THEN(DO) CHKOBJ OBJ(&LIB) OBJTYPE(*LIB) MONMSG MSGID(CPF9801) EXEC(DO) SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Output + library' *BCAT &LIB *BCAT 'not found') MSGTYPE(*ESCAPE) RETURN ENDDO ENDDO ENDDO LOOP: + CHGVAR VAR(&OFFSET) VALUE(&OFFSET + 55) CHGVAR VAR(&REMAINS) VALUE(1431 - &OFFSET) IF COND(%SST(&STM &OFFSET 1) *EQ ' ' *AND %SST(&STM &OFFSET 2) + *NE ' ') THEN(CHGVAR VAR(&STM) VALUE(%SST(&STM 1 &OFFSET) + *CAT %SST(&STM &OFFSET &REMAINS))) IF COND(&OFFSET *LT 1375) THEN(GOTO CMDLBL(LOOP)) /* Run QM Query */ RMVMSG CLEAR(*ALL) IF COND(&OUTPUT *EQ '*OUTFILE') THEN(DO) /* create collection runsqlstm@ if necessary */ CHKOBJ OBJ(RUNSQLSTM@) OBJTYPE(*LIB) MONMSG MSGID(CPF9801) EXEC(DO) SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Creating + collection RUNSQLSTM@. Please wait') TOPGMQ(*EXT) + MSGTYPE(*STATUS) STRQMQRY QMQRY(SQL001QQA) SETVAR((A 'RUNSQLSTM@')) MONMSG MSGID(CPF0000) EXEC(DO) SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Errors + found during creation of collection RUNSQLSTM@. See + detailed messages') MSGTYPE(*ESCAPE) RETURN ENDDO ENDDO STRQMQRY QMQRY(SQL001QQ) OUTPUT(&OUTPUT) + OUTFILE(RUNSQLSTM@/&FILE) OUTMBR(&MBR &MBROPT) SETVAR((A + %SST(&STM 1 55)) (B %SST(&STM 56 55)) (C %SST(&STM 111 + 55)) (D %SST(&STM 166 55)) (E %SST(&STM 221 55)) (F + %SST(&STM 276 55)) (G %SST(&STM 331 55)) (H %SST(&STM 386 + 55)) (I %SST(&STM 441 55)) (J %SST(&STM 496 55)) (K + %SST(&STM 551 55)) (L %SST(&STM 606 55)) (M %SST(&STM 661 + 55)) (N %SST(&STM 716 55)) (O %SST(&STM 771 55)) (P + %SST(&STM 826 55)) (Q %SST(&STM 881 55)) (R %SST(&STM 936 + 55)) (S %SST(&STM 991 55)) (T %SST(&STM 1046 55)) (U + %SST(&STM 1101 55)) (V %SST(&STM 1156 55)) (W %SST(&STM + 1211 55)) (X %SST(&STM 1266 55)) (Y %SST(&STM 1321 55)) (Z + %SST(&STM 1376 55))) MONMSG MSGID(CPF0000) EXEC(DO) SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Errors + found in SQL statement. See detailed messages') + MSGTYPE(*ESCAPE) RETURN ENDDO CPYF FROMFILE(RUNSQLSTM@/&FILE) TOFILE(&LIB/&FILE) + MBROPT(*REPLACE) CRTFILE(*YES) MONMSG MSGID(CPF0000) EXEC(DO) CPYF FROMFILE(RUNSQLSTM@/&FILE) TOFILE(&LIB/&FILE) + MBROPT(*REPLACE) CRTFILE(*NO) ENDDO DLTF FILE(RUNSQLSTM@/&FILE) ENDDO ELSE CMD(DO) STRQMQRY QMQRY(SQL001QQ) OUTPUT(&OUTPUT) SETVAR((A %SST(&STM + 1 55)) (B %SST(&STM 56 55)) (C %SST(&STM 111 55)) (D + %SST(&STM 166 55)) (E %SST(&STM 221 55)) (F %SST(&STM 276 + 55)) (G %SST(&STM 331 55)) (H %SST(&STM 386 55)) (I + %SST(&STM 441 55)) (J %SST(&STM 496 55)) (K %SST(&STM 551 + 55)) (L %SST(&STM 606 55)) (M %SST(&STM 661 55)) (N + %SST(&STM 716 55)) (O %SST(&STM 771 55)) (P %SST(&STM 826 + 55)) (Q %SST(&STM 881 55)) (R %SST(&STM 936 55)) (S + %SST(&STM 991 55)) (T %SST(&STM 1046 55)) (U %SST(&STM + 1101 55)) (V %SST(&STM 1156 55)) (W %SST(&STM 1211 55)) (X + %SST(&STM 1266 55)) (Y %SST(&STM 1321 55)) (Z %SST(&STM + 1376 55))) MONMSG MSGID(CPF0000) EXEC(DO) SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Errors + found in SQL statement. See detailed messages') + MSGTYPE(*ESCAPE) RETURN ENDDO ENDDO ENDPGM 
 
 Free SQL and Improved Query Through Query Manageme
 
 Figure 4 QM query SQL001QQ
 
 &A&B&C&D&E&F&G&H&I&J&K&L&M&N&O&P&Q&R&S&T&U&V&W&X&Y&Z 
 
 Free SQL and Improved Query Through Query Manageme
 
 Figure 5 QM query SQL001QQA
 
 Figure 5: QM Query SQL001QQA ....+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 CREATE COLLECTION &A To compile: CRTQMQRY QMQRY(xxx/SQL001QQA) SRCFILE(xxx/QMQRYSRC) 

Don Denoncourt is a freelance consultant. He can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

---

MC Press books written by Don Denoncourt available now on the MC Press Bookstore.

		Java Application Strategies for iSeries and AS/400 Explore the realities of using Java to develop real-world OS/400 applications. List Price $89.00 Now On Sale

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions