The SQL command provides you with a command interface to SQL functions without using SQL,
similar to the ever familiar CPYF (Copy File) command.
This allows users and programmers with little knowledge of SQL syntax to perform simple to
fairly complex SQL functions.
The first parameter displayed on the command prompt is the SQL type.
This is as follows:
Prompt SQL commands (SQL)
Type choices, press Enter.
SQL type . . . . . . . . . . . . *LIST *LIST, *UPDATE, *INSERT...
The SQL command has a number of parameters that are displayed based on the SQL type parameter selected (TYPE).
The parameters for each type are as follows:
Prompt SQL commands (SQL)
Type choices, press Enter.
SQL type . . . . . . . . . . . . *LIST *LIST, *UPDATE, *INSERT...
Select (field) . . . . . . . . . * Name, *
+ for more values __________
From file . . . . . . . . . . . __________ Name
Library . . . . . . . . . . . *LIBL Name, *LIBL
File designator . . . . . . . __________ Name
+ for more values __________
*LIBL
__________
Include records by field test: _
Relationship . . . . . . . . . *NONE *IF, *AND, *OR, *NONE
Field name . . . . . . . . . . __________ Name
Relational operator . . . . . *EQ *EQ, *NE, *GT, *LT, *GE...
Value or field name . . . . . _________________________
+ for more values _
Group by (field) . . . . . . . . __________ Name
+ for more values __________
Order by (field) . . . . . . . . __________ Name
+ for more values __________
Output . . . . . . . . . . . . . * *, *PRINT, *OUTFILE
The six parameters associated with "*LIST" are; the fields you wish to include (SELECT); the 1-25 file names, libraries, and designators from which to view this data (FROM); the 0-25 field test conditions on which to select records for the list (WHERE); the 0-25 fields on which to group the list (GROUP); the 0-25 fields to sort the list by (SORT); and where to send the output (OUTPUT). The OUTPUT parameter is common to all types.
You can either enter up to 25 field names to include in the list or leave the default of "*" for all fields on the SELECT parameter.
The optional file designators in the FROM parameter list are to indicate from which files like-named fields are coming from. This is easily identified as the "T01, T02, ..." used automatically in the Query/400 tool. I suggest using the same naming convention here for consistency.
The parameters listed as 0-25 are optional and only there to aid in limiting the selection or for formatting. They are as follows:
The WHERE parameter is used for record selection and also to join key fields from multiple files. This parameter is a mixed list of 4 elements. They are the Relationship (*IF, *AND, *OR, or *NONE), the Field name to test, the Relational operator (*EQ, *NE, *GT, *LT, *GE, *LE, *LIKE, *NLIKE), and the Value or Field name for the test. See "Special Restrictions for the WHERE or SET parameters" later in this manual for entering values in this element.
The GROUP parameter can be used to summarize the listing by the list of fields entered here. SQL, however, does not like it if you try to include fields on the list that are not included in this parameter. You would have to use Query/400 for that purpose. As well, the fields listed here must all be in the SELECT list.
The SORT parameter can be used to enter a list of up to 25 fields by which to sort the listing. These will be in ascending order (ASC). To sort any of the fields in descending order, include the value "DESC" (without the quotes) after the field name in the equivalent free-format RUNSQL command string. Each field listed here must also be included in the SELECT list. I find that SQL is typically used for updating information in data files, and therefore ascending sequence should be sufficient. Usually for medium to fairly complex listings, I would tend to use the Query/400 tool. I am considering an enhancement to allow Ascending/Descending sequence on this parameter but have not included it at this time.
Prompt SQL commands (SQL)
Type choices, press Enter.
SQL type . . . . . . . . . . . . > *UPDATE *LIST, *UPDATE, *INSERT...
File name . . . . . . . . . . . __________ Name
Library . . . . . . . . . . . *LIBL Name, *LIBL
File designator . . . . . . . __________ Name
Set: _
Field name . . . . . . . . . . __________ Name
To value or field name . . . . _________________________
+ for more values _
Include records by field test: _
Relationship . . . . . . . . . *NONE *IF, *AND, *OR, *NONE
Field name . . . . . . . . . . __________ Name
Relational operator . . . . . *EQ *EQ, *NE, *GT, *LT, *GE...
Value or field name . . . . . _________________________
+ for more values _
The four parameters associated with "*UPDATE" are; the file name, library, and designator from which to update (FILE); the 0-25 fields and values to update each field to (SET); the 0-25 field test conditions on which to select records to update (WHERE); and where to send the output (OUTPUT). The OUTPUT parameter is common to all types.
The only required element on the FILE parameter is the file name. The defaults of *LIBL for the library and the designator do not need to be entered if the defaults are desired.
At least one field name and value are required for the SET parameter. See "Special Restrictions for the WHERE or SET parameters" later in this manual for entering values in this parameter.
The WHERE parameter is used for record selection. This is the same parameter as the WHERE for the *LIST type.
Prompt SQL commands (SQL)
Type choices, press Enter.
SQL type . . . . . . . . . . . . > *INSERT *LIST, *UPDATE, *INSERT...
File name . . . . . . . . . . . __________ Name
Library . . . . . . . . . . . *LIBL Name, *LIBL
File designator . . . . . . . __________ Name
Set: _
Field name . . . . . . . . . . __________ Name
To value or field name . . . . _________________________
+ for more values _
The three parameters associated with "*INSERT" are; the file name, library, and designator from which to add records (FILE); the 0-25 fields and values to insert (SET); and where to send the output (OUTPUT). The OUTPUT parameter is common to all types.
As with the *UPDATE type, at least one FILE name has to be entered.
At least one field name and value are required for the SET parameter. See "Special Restrictions for the WHERE or SET parameters" later in this manual for entering values in this parameter.
Prompt SQL commands (SQL)
Type choices, press Enter.
SQL type . . . . . . . . . . . . > *DELETE *LIST, *UPDATE, *INSERT...
File name . . . . . . . . . . . __________ Name
Library . . . . . . . . . . . *LIBL Name, *LIBL
File designator . . . . . . . __________ Name
Include records by field test: _
Relationship . . . . . . . . . *NONE *IF, *AND, *OR, *NONE
Field name . . . . . . . . . . __________ Name
Relational operator . . . . . *EQ *EQ, *NE, *GT, *LT, *GE...
Value or field name . . . . . _________________________
+ for more values _
The three parameters associated with "*DELETE" are; the file name, library, and designator from which to delete records (FILE); the 0-25 field test conditions on which to select records to delete (WHERE); and where to send the output (OUTPUT). The OUTPUT parameter is common to all types.
As with the *UPDATE type, at least one FILE name has to be entered.
The WHERE parameter is used for record selection. This is the same parameter as the WHERE for the *LIST type.
Certain restrictions apply when entering values for the "Value" elements of either the WHERE or the SET parameter.
If entering a field name in the "Value" element, it has to be entered as '&field' just as one would enter a field in the Program variable element in the ADDBKP command for a CL program. This is to identify to the program that this is actually a field name and not a substitution variable (allowed in a compiled CL program). OS/400 will not allow it unquoted from the command line.
Additionally, if entering a totally numeric value for a field that is defined in the file as alphanumeric, but contains totally numeric values such as alpha date fields, use double quotes as in "nnnn" (e.g., WHERE((*IF XYDATE *EQ "950831")) ). If you use double quotes on any ALPHA string for these parameters it will be accepted, so if in doubt double-quote it. Numeric values for numeric fields must NOT be double-quoted or an error will result.
How SQL works
The SQL command does most of its processing using the CPP (Command Processing Progam) of the same name. It calls the program SQLEXT simply to extract the parameter values from the parameter lists. You could also use the command EXTLST from QUSRTOOL, but I included this program in the event that it is not present on your particular machine. SQLEXT is basically a copy of TAACLPDC (from source file QUSRTOOL/QATTCL) but is included to ensure that this utility is self-contained.
The CPP simply tries to validate the parameters entered and then format the appropriate RUNSQL request string. The validation routines include:
Upon validating the parameters, the program then formats the clauses for SQL based on the SQL Type parameter. It then formats the RUNSQL command, sends it to the external message queue, and executes the command. If an error occurs at this point, it will result in error codes from the QMQRY (Query Management Query) processor.
You are now ready to execute free-format SQL commands using RUNSQL -or- execute prompted SQL commands using SQL.
HINT: To learn native AS/400 SQL syntax, you can create a query (WRKQRY) and execute the following command to generate QMQRY command source:
RTVQMQRY QMQRY(YourQuery) SRCFILE(QGPL/QQMQRYSRC) ALWQRYDFN(*YES)
Have fun SQL'ing!