NOTE: This chapter explains using the most common features of SQL's data manipulation statements. Since SQL/PRO is ANSI compatible (uses industry-accepted standards), you can pick up any inexpensive SQL book form your local bookstore to gain more in-depth education about all the features and statements of SQL.
SQL on the AS/400 is a full function database language. With it you can create a database as well as manipulate the data it contains. You can also access an already existing database. For the purpose of this product we will only concentrate on those statements that allow you to manipulate an existing database. There are other books available which can teach you the full range of SQL.
Each of the data manipulation statements will be explained and information regarding restrictions and rules will be provided. It is not necessary to fully comprehend each rule before attempting to use the statements. However, we are providing the information here as a reference. You will find that the more you use SQL, the better your comprehension of it will become. Do not become discouraged if you don't fully understand each topic. Just try using the statements, and the meanings will become clearer.
Most of the limits set by SQL are at such an extreme, that most AS/400 shops will never encounter them. Also, for practical purposes, you probably would not want to reach the level of complexity that would exceed those limits.
We will now start our discussion of the data manipulation language statements that are available within SQL/400.
The COMMENT ON Statement
The COMMENT ON statement adds or replaces comments in the catalog descriptions of tables, views packages (programs) or columns.
The syntax for the COMMENT ON statement is as follows:
COMMENT ON <TABLE> <TABLE NAME or VIEW NAME> IS <STRING CONSTANT>
<COLUMN> <TABLE NAME or VIEW NAME/COLUMN NAME>
<PACKAGE> <PACKAGE NAME>
Example:
COMMENT ON TABLE CUSTMAST IS 'TEST FILE'
The CREATE COLLECTION Statement
The CREATE COLLECTION statement defines a collection in which tables, views and indexes can be created. A collection is created as one of the following: A library (relates objects and allows you to find them by name), A catalog (contains descriptions and consists of a data dictionary and a set of views and logical files) or A Journal and journal receiver (a journal QSQJRN and journal receiver QSQJRN0001 is created in the collection and records changes to all tables created in the collection).
This syntax for the CREATE COLLECTION statement is as follows:
CREATE COLLECTION
The CREATE INDEX Statement
The CREATE INDEX statement creates an index on a table.
The syntax for the CREATE INDEX statement is as follows:
CREATE [UNIQUE OR WHERE NOT NULL] INDEX <INDEX NAME>
ON <TABLE NAME> (<COLUMN NAME> [ASC OR DESC])
The CREATE TABLE Statement
The CREATE TABLE statement allows you to create a table (i.e., physical file) and define the physical attributes of the columns in the table. When this statement is executed, a new, empty table is created with the column information as defined. The first parameter specifies the column name, the second parameter specifies the data type and length for that column and the third parameter specifies if the column can be null.
The syntax for the CREATE TABLE statement is as follows:
CREATE TABLE <TABLE NAME>
(<COLUMN NAME> <DATA TYPE>[(LENGTH)] [NOT NULL OR NOT NULL
WITH DEFAULT]
Example:
CREATE TABLE CUSTMAST
(CUSTNUM NUMERIC (8) NOT NULL,
CUSTNAM CHAR (28) NOT NULL,
ADDR1 CHAR (38) NOT NULL,
ADDR2 CHAR (38) ,
SLSAMT DECIMAL (8, 2))
The CREATE VIEW Statement
The CREATE VIEW statement will create a subset of a file or table. Creating a view is very much like creating a table, in so much as you must define the attributes of the columns. You must have at least one of the following authorities over the table or file in order to create a view of it: SELECT, UPDATE, INSERT or DELETE.
If you do not specify column names in the view, then the names are the same as those in the table or file you are creating the view from. Since the view is totally dependent on one or more tables or files for data, it has no data of its own, therefore it requires no storage for the data. Consequently when you update the data in a view, you are actually updating the data in the table or file.
The syntax for the CREATE VIEW statement is as follows:
CREATE VIEW <VIEW NAME> [(COLUMN NAME)]
AS <SUBSELECT>
Example:
CREATE VIEW CUSTLIST (CUSTNUM, CUSTNAM)
AS SELECT CUSTNUM, CUSTNAM
FROM CUSTMAST
WHERE CUST NUM > 20000001
The DROP Statement
The DROP statement deletes an object. Any object that is directly or indirectly dependent on that object is also deleted.
The GRANT statement grants the privilege to execute statements in a package.
The syntax for the GRANT statement is as follows:
GRANT EXECUTE
ON PACKAGE <PACKAGE NAME>
TO <AUTHORIZATION NAME>
The LABEL ON Statement
The LABEL ON statement adds or replaces labels in the catalog descriptions of tables, views, packages or columns.
The syntax for the LABEL ON statement is as follows:
LABEL ON <TABLE> <TABLE NAME> IS <STRING CONSTANT>
<COLUMN> <COLUMN NAME>
<VIEW> <VIEW NAME>
<PACKAGE> <PACKAGE NAME>
The LOCK TABLE Statement
The LOCK TABLE statement acquires a shared or exclusive lock on a specified table. The share mode prevents any concurrent application processes to be executed on the table with the exception of read only operations. The exclusive mode prevents any concurrent operations at all to be executed on the table.
The syntax for the LOCK TABLE statement is as follows:
LOCK TABLE <TABLE NAME> IN <SHARE OR EXCLUSIVE> MODE
The REVOKE Statement
The Revoke statement removes privileges on a table or view. It can remove all privileges or one or more of the following privileges: SELECT, DELETE, INDEX, INSERT or UPDATE.
The syntax for the REVOKE statement is as follows:
REVOKE <PRIVILEGES> ON <TABLE NAME> FROM <AUTHORIZATION NAME>
[<ALL>] <VIEW NAME>
The SELECT Statement
The SELECT statement allows you to read or list records from a file or files. You can condition how records are selected, sort the resulting table, perform group summary operations and join files together.
The syntax for the SELECT statement is as follows:
SELECT [DISTINCT | ALL <value expression>,...
FROM {<library/file>} [<alias>,...
[WHERE <predicate>]
[GROUP BY {<field name>,...}]
[HAVING <predicate>]
[UNION | ALL]
[ORDER BY {<field name> | <relative field position>},...]
The clauses above are listed in the order in which they can be used. You are not required to use any of these, except the SELECT and FROM clauses. However, if you do use any, they must be in the order listed.
SELECT Clause
The Select clause is used to specify the fields to be presented in the resulting table. You can select all of the fields for the files listed in the FROM clause by using an "*" rather than field names. You can also provide your own fields by means of arithmetic functions, built-in functions, column functions, or literals. The total number of fields allowed is 8,000. Also, the total length of all fields selected cannot exceed 32,766 characters. The fields must be separated by commas.
You cannot use the "*" (all fields) and at the same time specify other fields. They are mutually exclusive. If you use column functions, any fields that are not part of a column function must be specified in the GROUP BY clause.
The DISTINCT keyword on the SELECT clause specifies that you do not want duplicate records in the resulting table. This is the same as using UNIQUE on a file DDS. By specifying DISTINCT< the resulting table will be sorted. The file will be sorted in the order of the fields in the SELECT clause unless you use the ORDER BY clause to specify the sort order.
In the following examples, examine each statement and look at the results to get a feel for how the statement processes the data.
Example 1: Assume that the catalog master file contains the following data.
CATLGMST -Catalog Master File
CATLG#
CATDSC
CATDTE
CTGRY#
CYSLS$
12-448-EX2
Sports Coat
021590
13
4879.52
44-ESD-148
Baseball Glove
031490
15
278.78
A1-1258-12
AM/FM Radio
120189
01
1763.48
55-78W-12A
China
031791
10
2248.97
31-WE8-AW8
Aluminum Bat
081488
15
879.66
27-222-158
Tennis Racket
013092
15
148.79
P7-4W8-789
Necklace
042290
14
3915.01
2W-877-WW2
Dress
060591
22
500.00
2W-877-XX1
Basket Ball
111189
15
4987.88
36Q-456-EQ
VCR
120191
01
8793.45
SELECT *
FROM CATLGMST
This statement lists every field for every record in the catalog master file. The records are not sorted, but are read in arrival sequence. The resulting table would look just like the original file.
SELECT CATLG#, CATDSC
FROM CATLGMST
This statement will list every catalog number and description in the catalog master file. The records are not sorted.
Resulting table:
CATLG#
CATDSC
12-448-EX2
Sports Coat
44-ESD-148
Baseball Glove
A1-1258-12
AM/FM Radio
55-78W-12A
China
31-WE8-AW8
Aluminum Bat
27-222-158
Tennis Racket
P7-4W8-789
Necklace
2W-877-WW2
Dress
2W-877-XX1
Basket Ball
36Q-456-EQ
VCR
SELECT *, CYSLS$+LYSLS$
FROM CATLGMST
This statement is invalid since the "*" is used with field names in the SELECT clause.
Example 2: Assume that the catalog master file contains the following data.
CATLGMST-Catalog Master File
CATLG#
CYSLS$
LYSLS$
12-448-EX2
4879.52
7894.22
44-ESD-148
278.78
1489.77
A1-1258-12
1763.48
1589.44
55-78W-12A
2248.97
789.88
31-WE8-AW8
879.66
444.48
27-222-158
148.79
0.00
P7-4W8-789
3915.01
3879.11
2W-877-WW2
550.00
2489.45
2W-877-XX1
4987.88
7265.37
36Q-456-EQ
8793.45
4276.40
SELECT CATLG#, 'Total Sales:', CYSLS$+LYSLS$
FROM CATLGMST
This statement will list each catalog number in the catalog master file and the sum of the current sales and last year's sales. Also, a literal of 'Total Sales:' will be listed for each catalog number.
Resulting Table:
CATLG#
CYSLS$+LYSLS$
12-448-EX2
Total Sales:
12773.74
44-ESD-148
Total Sales:
1768.55
A1-1258-12
Total Sales:
3352.92
55-78W-12A
Total Sales:
3038.85
31-WE8-AW8
Total Sales:
1324.14
27-222-158
Total Sales:
148.79
P7-4W8-789
Total Sales:
7794.12
2W-877-WW2
Total Sales:
2989.45
2W-877-XX1
Total Sales:
12253.25
36Q-456-EQ
Total Sales:
13069.85
Example 3: Assume that the sales history file contains the following data:
SALESHST-Sales History File
SLSO#
SLSCS#
SLCLG#
13478
00101
12-448-EX2
15897
02548
44-ESD-148
13478
00101
A1-1258-12
12489
03378
12-448-EX2
11457
02143
31-WE8-AW8
14999
00101
27-222-158
16678
03378
P7-4W8-789
14875
00668
12-448-148
15155
02549
P7-4WS-789
17891
02548
44-ESD-148
SELECT DISTINCT SLSCS#, SLCLG#
FROM SALESHST
This statement will list every unique occurrence of the customer number and catalog number in the sales history file. The resulting table will be sorted in the order of SLSCS# and SLCLG.
Notice that order numbers 15897and 17891 produced only one record in the table. This is because the customer and catalog numbers were the same.
Resulting Table:
SLSCS#
SLCLG#
00101
A1-1258-12
00101
12-448-EX2
00101
27-222-158
00668
12-448-148
02548
44-ESD-148
02549
P7-4W8-789
03378
P7-4W8-789
03378
12-448-EX2
05143
31-WE8-AW8
FROM Clause
The FROM clause is used to specify the names of the physical and/or logical files from which the resulting table is derived. You can join up to 32 files, although, it is not recommended to join more than 7 or 8 files for performance reasons. A file can also be joined to itself. The files must be separated by commas. If you wish to specify which library the file is in, use the normal AS/400 library qualifier syntax. If your file and/or library has an embedded period (.), you must then embed the file and/or library within double quotes (" ") in the FROM clause.
You cannot process a file that has more than one record format. Only the *FIRST (first created) member of a file will be processed. However, if a file has more than one member, you can perform an OVRDBF (Override Database File) command to point to the member you wish to use. You can also override a file to a specific library by means of the OVRDBF command. When you do this, the library/file specified on the OVRDBF command will be used, regardless of whether you qualify the file name in the FROM clause.
You can easily join files with the SELECT statement by specifying the files and which field in the files are the match fields. You are not required to have a one-to-one relationship. For example, you can join three files in a one-to-many-to-many relationship.
When joining files you must be careful to make sure that the logic used to link the files is correct. For example, if you are joining two files and do not specify how to link them, the resulting table will contain every combination of records possible. In other words, each record in the first file will be joined with every record in the second file. If you have two files with 2,000 and 6,000 records respectively, the resulting table will contain 12,000,000 records! Also, you must be careful how you use equal and not equal conditioning when joining files.
If any fields from the files to be joined have the same name and you wish to use those fields, you must qualify the field names with the name of the file from which the data is to be retrieved. This is true no matter where the field name is used. To qualify the field, you use the file name, a period, then the field name (e.g. CATLGMST.CTGRY#, PRODLIB/CATLGMST.CTGRY#).
To save keying the file name each time, you can use a correlated name for each file. This is an alias for the file name. Correlated names are also needed if you wish to process data from one level of a SELECT to a lower level. This will be explained later in this chapter. The syntax for specifying a correlated name is: file name, one space, correlated name. The maximum length of a correlated name is 10 characters.
Example 1: Assume that the catalog master file and the category master file contain the following data.
CATLGMST-Catalog Master File
CATLG#
CATDSC
CATDTE
CTGRY#
CYSLS$
12-448-EX2
Sports Coat
021590
13
4879.52
44-ESD-148
Baseball Glove
031490
15
278.78
A1-1258-12
AM/FM Radio
120189
01
1763.48
55-78W-12A
China
031791
10
2248.97
31-WE8-AW8
Aluminum Bat
081488
15
879.66
27-222-158
Tennis Racket
013092
15
148.79
P7-4W8-789
Necklace
042290
14
3915.01
2W-877-WW2
Dress
060591
22
500.00
2W-877-XX1
Basket Ball
111189
15
4987.88
36Q-456-EQ
VCR
120191
01
8793.45
CTGRYMST-Category Master File
CTGRY#
CTDESC
13
Men's Suits
15
Sporting Goods
01
Electronics
10
Dinnerware
14
Jewelry
22
Women's Apparel
SELECT CATLG#, CATLGMST.CTGRY#, CTDESC
FROM CATLGMST, CTGRYMST
WHERE CATLGMST.CTGRY# = CTGRYMST.CTGRY#
This statement will link the catalog master file and category master file and list the catalog number, its category and description for that category. This is a one-to-one relationship.
Resulting Table:
CATLG#
CTGRY#
CTDESC
12-448-EX2
13
Men's Suits
44-ESD-148
15
Sporting Goods
A1-1258-12
01
Electronics
55-78W-12A
10
Dinnerware
31-WE8-AW8
15
Sporting Goods
27-222-158
15
Sporting Goods
P7-4W8-789
14
Jewelry
2W-877-WW2
22
Women's Apparel
2W-877-XX1
15
Sporting Goods
36Q-456-EQ
01
Electronics
SELECT CATLG#, A.CTGRY#, CTDESC
FROM CATLGMST A, CTGRYMST B
WHERE A.CTGRY# = B.CTGRY#
This statement is the same as the previous example, but this one uses a correlated name instead of the file names. As you can see, this would be much easier to work with.
Example 2: Assume that the order header and order item files contain the following data.
ORDERHDR-Order Header File
OHORD#
OHCST#
13478
00101
15897
02548
12489
03378
11457
05143
14999
00101
16678
03378
14875
00668
15155
02549
17891
02548
ORDERITM-Order Item File
OHORD#
OICLG#
OIOQTY
OISQTY
13478
12-448-EX
200
195
15897
44-ESD-148
22
22
13478
A1-1258-12
134
144
12489
12-448-EX2
300
300
11457
31-WE8-AW8
15
0
14999
27-222-158
36
36
16678
P7-4W8-789
19
0
14875
12-448-148
87
87
15155
P7-4W8-789
112
0
17891
44-ESD-148
2145
0
SELECT OHORD#, OHCST#, OICLG#, OIOQTY, OISQTY
FROM ORDERHDR, ORDERITM
WHERE OHORD# = OIORD#
This statement will link each record in the order header file to each record in the order item file where the order numbers are the same. This is a one-to-many relationship.
Resulting Table:
OHORD#
OHCST#
OICLG#
OIOQTY
OISQTY
13478
00101
12-448-EX
200
195
15897
00101
44-ESD-148
22
22
13478
02548
A1-1258-12
134
144
12489
03378
12-448-EX2
300
300
11457
05143
31-WE8-AW8
15
0
14999
00101
27-222-158
36
36
16678
03378
P7-4W8-789
19
0
14875
00668
12-448-148
87
87
15155
02549
P7-4W8-789
112
0
17891
02548
44-ESD-148
2145
0
Example 3: Assume that the catalog master file contains the following data.
CATLGMST-Catalog Master File
CATLG#
CATDSC
CATSUB
12-448-EX
Sports Coat
44-ESD-148
Baseball Glove
A1-1258-12
AM/FM Radio
55-78W-12A
China
31-WE8-AW8
Aluminum Bat
A9-457-121
Clock Radio
A1-1258-12
27-222-158
Tennis Racket
P7-4W8-789
Necklace
2W-877-WW2
Dress
31-WE8-SX9
Wood Bat
31-WE8-AW8
2W-877-XX1
Basket Ball
36Q-456-EQ
VCR
SELECT A. CATLG#, A.CATDSC, A. CATSUB, B. CATDSC
FROM CATLGMST A, CATLGMST B
WHERE A. CATSUB<> ' '
AND A. CATSUB = BB.CATLG#
This statement will list every record that has a substitute catalog number and it will list the description for that catalog number along with the description of the substitute number. It does this by joining itself to itself.
Resulting Table:
CATLG#
CATDSC
CATSUB
CATDSC
A9-457-121
Clock Radio
A1-1258-12
AM/FM Radio
31-WE8-SX9
Wood Bat
31-WE8-AW8
Aluminum Bat
WHERE Clause
The WHERE clause is used to specify the conditions for selecting records from the files listed in the FROM clause. This is also where you specify how to link files together. Records are selected when the WHERE clause is true.
The WHERE clause provides us with much of the flexibility found within SQL. The ability to select records by means of simple or complex conditions, as well as more complicated testing, is a great help to the AS/400 programmer.
One of the more powerful functions of the WHERE clause is the use of a Sub-SELECT. We will spend a great deal of time explaining this function later in this chapter.
First, however, we will cover the more basic conditioning that is allowed and some of the special operators that are available in SQL. We will spend more time on these operators as well as SQL functions in chapter four.
Simple Conditioning
As with any logic for selecting records, you need the ability to perform relational tests between values. The values tested can be fields, calculated values or values derived via built-in functions. You also need the ability to perform multiple relational tests to determine whether records should be selected. The following are the operators that are available:
Comparison Operators
= Equal to
¬= Not equal
<> Not equal
> Greater than
¬> Not greater than
< Less than
¬< Not less than
>= Greater than or equal to
<= Less than or equal to
Logical Operators
AND conditioning
OR conditioning
It should be noted that you could also use parentheses to group your logic. Built-in functions can also be used as part of your relationship tests.
Example:
SELECT *
FROM CATLGMST
WHERE CTGRY# = 15
AND (CYSLS$ <> 0 OR LYSLS$ <> 0)
This statement would select all the fields from the catalog master file where the category number is 15 and either the current or last year's sales was not zero.
Complex Conditioning
SQL also provides some additional operators for selecting records. The following are the operators available and how they are used.
BETWEEN
The BETWEEN operator allows us to test whether a value or expression is between two different values, inclusively. The syntax for the BETWEEN operator is:
Value BETWEEN value AND value
The values used can be any field or a calculated value. This operator is valid for both numeric and character data.
It is up to you to insure that the range specified is valid. If the second value (after the AND) is less than the first value, no records would be selected. No error is indicated when this occurs.
Examples:
SELECT *
FROM CATLGMST
WHERE CTGRY# BETWEEN 1 AND 13
This statement would select all the records in the catalog master file where the category number is between 1 and 13. This would be the equivalent of saying:
WHERE CTGRY# >= 1 AND CTGRY# <= 13
________________________________________________
SELECT *
FROM CUSTMAST
WHERE CSTZIP BETWEEN '44000' AND '44999'
This statement would select all the records in the customer master file where the zip code is between '44000' and '44999.' This would be the same as saying that the zip code begins with '44'. Because CSTZIP is a character field, single quotes or apostrophes are used when specifying the range.
________________________________________________
SELECT *
FROM CATLGMST
WHERE LYSLS$ BETWEEN L2SLS$ AND CYSLS$
This statement would select all the records in the catalog master file where last years sales is between two years ago and the current years sales. If the field LYSLS$ is later than CYCLS$ or earlier than L2SLS$, the record would not be selected.
LIKE
The LIKE operator allows us to test a string value to see if it contains certain characters. There are two special characters that you can use to specify how to look for the characters. The first special character is %, which indicates any number of missing characters. The second is __, which indicates one character only. By combining the % and _, you can find any combination of characters.
When using the LIKE operator there is no provision made for translating a string value. Therefore, the LIKE operator is case sensitive. The syntax for the LIKE operator is:
value LIKE 'search characters'
Examples:
SELECT *
FROM CATLGMST
WHERE CATDSC LIKE '%Apparel%'
This statement would select all the records in the catalog master file where the description contains the word "Apparel." There can be any number of characters prior to the word and any number of characters after the word.
SELECT *
FROM CATLGMST
WHERE CATDSC LIKE ' _o%
This statement would select all the records in the catalog master file where the second character in the description is the letter "o."
IN
The IN operator allows you to test a value to see if it exists in a list of values. The list can either be provided or can be derived from a Sub-SELECT. The Sub-SELECT will be explained a little later in this chapter. The syntax for the IN operator is:
Value IN (value list or Sub-SELECT)
This operator is valid for both numeric and character data. When you provide the values to be tested, they must be separated by commas.
Example:
SELECT *
FROM CUSTMAST
WHERE CSTSTA IN ('AZ, 'NM', 'CO', 'WY')
This statement would select all the records in the customer master file where the state is either Arizona, New Mexico, Colorado or Wyoming.
EXISTS
The EXISTS operator will test to see if any records exist in a Sub-SELECT. If any records were selected by the Sub-SELECT, the condition is true. The syntax for the EXISTS operator is:
EXISTS (Sub-SELECT)
Examples will be provided later in this chapter.
NOT
The NOT operator can be used in connection with the following operators: BETWEEN,IN, LIKE and EXISTS. The NOT operator performs a negative test. If the operator is true, the NOT will make it false and vice-versa.
Examples:
SELECT *
FROM CATLGMST
WHERE CTGRY# NOT BETWEEN 1 AND 13
This statement will select all the records in the catalog master file where the category number is not between 1 and 13.
1 SELECT *
FROM CUSTMAST
WHERE CSTSTA NOT IN ('AZ, 'NM', 'CO', 'WY')
This statement would select all the records in the customer master file where the state is not Arizona, New Mexico, Colorado and Wyoming.
SOME
The SOME operator allows us to use a comparison operator against a Sub-SELECT and see if the condition is true for some of the values. The operator will prove true if the condition is true for at least one value. This operator is valid only with a Sub-SELECT. Examples will be provided later in this chapter.
ANY
The ANY operator is the same as the SOME operator.
ALL
The ALL operator allows us to use a comparison operator against a Sub-SELECT and see if the condition is true for all of the values. The operator will prove true if the condition is true for all of the values. This operator is valid only with a Sub-SELECT. Examples will be provided later in this chapter.
WHERE Clause Examples
Now that we have considered the operators allowed within the WHERE clause, we will now provide some simple examples showing how the WHERE clause works.
Example 1: Assume that the catalog master file contains the following data.
CATLGMST - Catalog Master File
CATLG#
CATDSC
CATDTE
CTGRY#
CYSLS$
12-448-EX2
Sports Coat
021590
13
4879.52
44-ESD-148
Baseball Glove
031490
15
278.78
A1-1258-12
AM/FM Radio
120189
01
1763.48
55-78W-12A
China
031791
10
2248.97
31-WE8-AW8
Aluminum Bat
081488
15
879.66
27-222-158
Tennis Racket
013092
15
148.79
P7-4W8-789
Necklace
042290
14
3915.01
2W-877-WW2
Dress
060591
22
500.00
2W-877-XX1
Basket Ball
111189
15
4987.88
36Q-456-EQ
VCR
120191
01
8793.45
SELECT *
FROM CATLGMST
WHERE CTGRY# = 15
This will select all fields for records in the catalog master file where the catalog group number is equal to 15.
Resulting Table:
CATLG#
CATDSC
CATDTE
CTGRY#
CYSLS$
44-ESD-148
Baseball Glove
031490
15
278.78
31-WE8-AW8
Aluminum Bat
081488
15
879.66
27-222-158
Tennis Racket
013092
15
148.79
2W-877-XX1
Basket Ball
111189
15
4987.88
SELECT *
FROM CATLGMST
WHERE CTGRY# = 15
AND CYSLS$ > 500
This will select all fields for records in the catalog master file where the catalog group number is equal to 15 and the current year sales is greater from $500.00.
Resulting Table:
CATLG#
CATDSC
CATDTE
CTGRY#
CYSLS$
31-WE8-AW8
Aluminum Bat
081488
15
879.66
2W-877-XX1
Basket Ball
111189
15
4987.88
SELECT *
FROM CATLGMST
WHERE CTGRY# = 15
AND SUBSTR (CATLG#, 1,1) = '2'
OR CYSLS$ > 500
This will select all fields for records in the catalog master file where the catalog group number is equal to 15 and the first character of the catalog number is a "2" or if the current year sales is greater than $500.00.
Resulting Table:
CATLG#
CATDSC
CATDTE
CTGRY#
CYSLS$
12-448-EX2
Sports Coat
021590
13
4879.52
A1-1258-12
AM/FM Radio
120189
01
1763.48
55-78W-12A
China
031791
10
2248.97
27-222-158
Tennis Racket
013092
15
148.79
P7-4W8-789
Necklace
042290
14
3915.01
2W-877-XX1
Basket Ball
111189
15
4987.88
36Q-456-EQ
VCR
120191
01
8793.45
GROUP BY Clause
The GROUP BY clause produces a resulting table by grouping records according to the fields specified on this clause. These fields determine when a group record is produced. This is the same as a group summary operation. In order to use the GROUP BY clause, a column function must be present in the SELECT clause. The fields must be separated by commas.
The fields specified here can be any field in the files listed in the FROM clause. Any fields that are specified in the SELECT clause, which is not part of a column function, must be specified here. However, you can specify field names in the GROUP BY clause, which are not in the SELECT clause. Caution must be used when you do this, since the field names listed in the GROUP BY clause are not presented in the resulting table. In other words, only those fields in the SELECT clause are listed. GROUP BY fields provides the mechanism for causing level breaks for the column functions.
The use of built-in functions or arithmetic operations is not allowed in the GROUP BY clause.
Example 1: Assume that the catalog master file contains the following data.
CATLGMST - Catalog Master File
CATLG#
CTGRY#
CYSLS$
LYSLS$
12-448-EX2
13
4879.52
7894.22
44-ESD-148
15
278.78
1489.77
A1-1258-12
01
1763.48
1589.44
55-78W-12A
10
2248.97
789.88
31-WE8-AW8
15
879.66
444.48
27-222-158
15
148.79
0.00
P7-4W8-789
14
3915.01
3879.11
2W-877-WW2
22
550.00
2489.45
2W-877-XX1
15
4987.88
7265.37
36Q-456-EQ
01
8793.45
4276.40
SELECT CTGRY#, SUM (CYSLS$)
FROM CATLGMST
GROUP BY CTGRY#
The statement will list the total current year sales for each category in the catalog master file.
Resulting Table:
CTGRY#
SUM (CYSLS$)
01
10556.93
10
2248.97
13
4879.52
14
3915.01
15
6295.11
22
500.00
SELECT SUBSTR (CATLG#, 1,5,), SUM (CYSLS$ + LYSLS$)
FROM CATLGMST
GROUP BY CATLG#
This statement would provide the total current year plus last year's sales for each catalog number. However, the resulting table will only show the first 5 characters of the catalog number.
Resulting Table:
CATLG#
SUM(total)
A1-12
3352.92
P7-4W
7794.12
12-44
12773.74
2W-87
2989.45
2W-87
12253.25
27-22
148.79
31-WE
1324.14
36Q-4
13069.85
44-ES
1768.55
55-78
3038.85
SELECT CATLG#, SUM (CYSLS$ + LYSLS$)
FROM CATLGMST
GROUP BY SUM (CYSLS$ + LYSLS$), CATLG#
This statement is invalid. Calculated fields are not allowed in the GROUP BY clause.
HAVING Clause
The HAVING clause further defines the resulting table by applying conditional test to each group record produced. The HAVING clause works exactly like the WHERE clause. The operators allowed in the WHERE clause are also allowed in the HAVING clause. A simple rule to remember is WHERE chooses records, HAVING chooses groups.
A column function must exist in the SELECT clause in order to use the HAVING clause. The group record will only be provided if the HAVING condition is true. You are not required to have a GROUP BY with the HAVING clause, but normally you would.
When specifying the test conditions, you must use a column function in the HAVING clause. The column function specified can be for any field(s) in the files. You cannot condition a group by testing non-column function fields.
You are allowed to use a Sub-SELECT within the HAVING clause.
Example 1: Assume that the catalog master file contains the following data.
CATLGMST - Catalog Master File
CATLG#
CTGRY#
CYSLS$
LYSLS$
12-448-EX2
13
4879.52
7894.22
44-ESD-148
15
278.78
1489.77
A1-1258-12
01
1763.48
1589.44
55-78W-12A
10
2248.97
789.88
31-WE8-AW8
15
879.66
444.48
27-222-158
15
148.79
0.00
P7-4W8-789
14
3915.01
3879.11
2W-877-WW2
22
550.00
2489.45
2W-877-XX1
15
4987.88
7265.37
36Q-456-EQ
01
8793.45
4276.40
SELECT CTGRY#, SUM (CYSLS$ + LYSLS$)
FROM CATLGMST
GROUP BY CTGRY#
HAVING SUM (CYSLS$ + LYSLS$) > 7000
This statement would provide the total current year plus last year sales for each category number where the total sales for that category is greater than $7,000.
Resulting Table:
CTGRY#
SUM(total)
01
16422.77
13
12773.74
14
7794.12
15
15494.73
UNION Clause
The UNION clause indicates that you wish to combine the results of two or more SELECT statements into one resulting table. Duplicate records in the final resulting table will be dropped, unless you specify the ALL keyword on the UNION clause. The resulting table will be sorted based upon either, the order of the fields in the resulting table or the fields specified on the ORDER BY clause. If you use the ALL keyword, no sort on the resulting table will be performed unless the ORDER BY clause is specified.
The fields listed on each SELECT clause must match in order and type. You cannot have a numeric value in the same column as a character value. The maximum number of SELECT statements that can be joined together is 32. Also, the combined number of files that can be specified (joined or otherwise) for all the SELECT statements is 32.
Example 1: Assume that the sales history and catalog master files contain the following data:
SALESHST - Sales History File
SLSO#
SLSCS#
SLSHPD
SLCLG#
13478
00101
960215
12-448-EX2
15897
02548
950624
44-ESD-148
13478
00101
950215
A1-1258-12
12489
03378
950213
12-448-EX2
11457
05143
941006
31-WE8-AW8
14999
00101
970315
27-222-158
16678
03378
970507
P7-4W8-789
14875
00668
970116
12-448-148
15155
02549
950328
P7-4W8-789
17891
02548
970109
44-ESD-148
CATLGMST - Catalog Master File
CATLG#
CATDSC
CATDTE
CTGRY#
CYSLS$
12-448-EX2
Sports Coat
021595
13
4879.52
44-ESD-148
Baseball Glove
031496
15
278.78
A1-1258-12
AM/FM Radio
120196
01
1763.48
55-78W-12A
China
031797
10
2248.97
31-WE8-AW8
Aluminum Bat
081496
15
879.66
27-222-158
Tennis Racket
013097
15
148.79
P7-4W8-789
Necklace
042295
14
3915.01
2W-877-WW2
Dress
060597
22
500.00
2W-877-XX1
Basket Ball
111196
15
4987.88
36Q-456-EQ
VCR
120197
01
8793.45
SELECT DISTINCT SLCLG#
FROM SALESHST
WHERE SLSHPD >= 970101
UNION
SELECT CATLG#
FROM CATLGMST
WHERE SUBSTR (DIGITS (CATDTE), 5, 2) >= '97'
This statement would produce a list of all catalog numbers that have been sold on or since January 1, 1997, or were introduced into the catalog master file after 1997.
Records Selected:
From SALESHST From CATLGMST
SLCLG#
CATLG#
P7-4W8-789
2W-877-WW2
12-448-148
27-222-158
27-222-158
36Q-456-EQ
44-ESD-148
55-78W-12A
Final Resulting Table:
P7-4W8-790
12-448-148
2W-877-WW2
27-222-158
36Q-456-EQ
44-ESD-148
55-78W-12A
If you used the UNION ALL instead of just the UNION, you would get the following resulting table.
P7-4W8-790
12-448-148
2W-877-WW2
27-222-158
27-222-158
36Q-456-EQ
44-ESD-148
55-78W-12A
ORDER BY Clause
The ORDER BY clause specifies how the resulting table will be sorted. The fields specified here must be in the SELECT clause. If you wish to sort on a calculated or derived field or on a SELECT statement that contains a UNION clause, you must use the field's relative position (column number) within the SELECT, instead of the field name. The ability to specify a field's relative position is valid only in the ORDER BY clause (see Example 2 in this section). The fields must be separated by commas.
The sort default for all fields will be ascending sequence. If you wish to specify descending sequence, you place the keyword DESC after the field name, separated by a space. You can also use the ASC keyword to specify ASCENDING sequence.
The table is sorted first by the first field on the ORDER BY clause and secondly by the second field on the ORDER BY clause, and so.
Example 1:Assume that the catalog master file contains the following data:
CATLGMST - Catalog Master File
CATLG#
CATDSC
CATDTE
CTGRY#
CYSLS$
12-448-EX2
Sports Coat
021590
13
4879.52
44-ESD-148
Baseball Glove
031490
15
278.78
A1-1258-12
AM/FM Radio
120189
01
1763.48
55-78W-12A
China
031791
10
2248.97
31-WE8-AW8
Aluminum Bat
081488
15
879.66
27-222-158
Tennis Racket
013092
15
148.79
P7-4W8-789
Necklace
042290
14
3915.01
2W-877-WW2
Dress
060591
22
500.00
2W-877-XX1
Basket Ball
111189
15
4987.88
36Q-456-EQ
VCR
120191
01
8793.45
SELECT *
FROM CATLGMST
ORDER BY CTGRY#, CATLG#
This statement produces a list of all fields for every record in the catalog master file. The list will be sorted by catalog number within category number.
Resulting Table:
CATLG#
CATDSC
CATDTE
CTGRY#
CYSLS$
A1-1258-12
AM/FM Radio
120189
01
1763.48
36Q-456-EQ
VCR
120191
01
8793.45
55-78W-12A
China
031791
10
2248.97
12-448-EX2
Sports Coat
021590
13
4879.52
P7-4W8-789
Necklace
042290
14
3915.01
2W-877-XX1
Basket Ball
111189
15
4987.88
27-222-158
Tennis Racket
013092
15
148.79
31-WE8-AW8
Aluminum Bat
081488
15
879.66
44-ESD-148
Baseball Glove
031490
15
278.78
2W-877-WW2
Dress
060591
22
500.00
SELECT *
FROM CATLGMST
ORDER BY CTGRY#, CATLG# DESC
This statement would produce a list of all fields for every record in the catalog master file. The list will be sorted by, descending catalog number within category number.
Resulting Table:
CATLG#
CATDSC
CATDTE
CTGRY#
CYSLS$
36Q-456-EQ
VCR
120191
01
8793.45
A1-1258-12
AM/FM Radio
120189
01
1763.48
55-78W-12A
China
031791
10
2248.97
12-448-EX2
Sports Coat
021590
13
4879.52
P7-4W8-789
Necklace
042290
14
3915.01
44-ESD-148
Baseball Glove
031490
15
278.78
31-WE8-AW8
Aluminum Bat
081488
15
879.66
27-222-158
Tennis Racket
013092
15
148.79
2W-877-XX1
Basket Ball
111189
15
4987.88
2W-877-WW2
Dress
060591
22
500.00
SELECT *
FROM CATLGMST
ORDER BY 4, 1 DESC
This statement is the same as the previous one. The difference is, you are using the field's relative position to sort the resulting table.
Example 2: Assume that the catalog master file contains the following data:
CATLGMST - Catalog Master File
CATLG#
CYLSL$
LYSLS$
12-448-EX2
4879.52
7894.22
44-ESD-148
278.78
1489.77
A1-1258-12
1763.48
1589.44
55-78W-12A
2248.97
789.88
31-WE8-AW8
879.66
444.48
27-222-158
148.79
0.00
P7-4W8-789
3915.01
3879.11
2W-877-WW2
500.00
2489.45
2W-877-XX1
4987.88
7265.37
36Q-456-EQ
8793.45
4276.40
SELECT CATLG#, CYSLS$ + LYSLS$
FROM CATLGMST
ORDER BY 2 DESC, CATLG# ASC
This statement would produce a list of the catalog number and total current year and last year's sales for every record in the catalog master file. The list will be sorted by catalog number within descending current year plus last year's sales.
Resulting Table:
CATLG#
CYLSL$+LYSLS$
12-448-EX2
13069.85
44-ESD-148
12773.74
A1-1258-12
12253.25
55-78W-12A
7794.12
31-WE8-AW8
3352.92
27-222-158
3038.85
P7-4W8-789
2989.45
2W-877-WW2
1768.55
2W-877-XX1
1324.14
36Q-456-EQ
148.79
Notice the use of the relative position number (2) to indicate that ordering should be done on the calculate field CYSLS$ + LYSLS$.
UPDATE Statement
The UPDATE statement is used to change the values of fields for a given file. When using the UPDATE statement, you cannot update more than one file at a time. Also, you cannot update a field with information from another file or record.
The syntax for the UPDATE statement is as follows:
UPDATE <[library/] file>
SET = , ...
[WHERE ]
The WHERE clause is the only optional clause in the UPDATE statement.
UPDATE Clause
The UPDATE clause specifies the name of the file that is to be updated. Only one file name can be specified. Therefore, you cannot join files the way you can do for the SELECT statement. The file name specified here, either directly or indirectly, cannot be used in any Sub-SELECT statements.
SET Clause
The SET clause is used to specify which fields are going to be updated and with what values. You can update a field with a calculated value or with a set value. The calculated value can be only from information in the same record. You cannot use column functions or scalar functions within the SET clause. Each field must be separated by a comma.
WHERE Clause
The WHERE clause is used to specify which records will be updated. If you do not specify the WHERE clause, all records in the file will be updated.
Example 1: Assume that the catalog master file contains the following data:
CATLGMST - Catalog Master File Before UPDATE
CATLG#
CTGRY#
CYSLS$
12-448-EX2
13
4879.52
44-ESD-148
15
278.78
A1-1258-12
01
1763.48
55-78W-12A
10
2248.97
31-WE8-AW8
15
879.66
27-222-158
15
148.79
P7-4W8-789
14
3915.01
2W-877-WW2
22
500.00
2W-877-XX1
15
4987.88
36Q-456-EQ
01
8793.45
UPDATE CATLGMST
SET CYSLS$ = 0
WHERE CTGRY# = 15
This statement would set the current year's sales field to zero for every record in the catalog master file where the category number is 15.
CATLGMST - Catalog Master File After UPDATE
CATLG#
CTGRY#
CYSLS$
12-448-EX2
13
4879.52
44-ESD-148
15
0.00
A1-1258-12
01
1763.48
55-78W-12A
10
2248.97
31-WE8-AW8
15
0.00
27-222-158
15
0.00
P7-4W8-789
14
3915.01
2W-877-WW2
22
500.00
2W-877-XX1
15
0.00
36Q-456-EQ
01
8793.45
Example 2: Assume that the catalog master file contains the following data:
CATLGMST - Catalog Master File Before UPDATE
CATLG#
CYSLS$
LYSLS$
L2SLS$
12-448-EX2
4879.52
7894.22
6106.12
44-ESD-148
278.78
1489.77
826.73
A1-1258-12
1763.48
1589.44
2431.64
55-78W-12A
2248.97
789.88
0.00
31-WE8-AW8
879.66
444.48
799.78
27-222-158
148.79
0.00
0.00
P7-4W8-789
3915.01
3879.11
3125.68
2W-877-WW2
500.00
2489.45
0.00
2W-877-XX1
4987.88
7265.37
6241.39
36Q-456-EQ
8793.45
4276.40
0.00
UPDATE CATLGMST
SET L2SLS$=LYSLS$
LYSLS$=CYSLS$,
CYSLS$=0
This statement would update the two years ago sales field with last year's sales, then last year's sales with the current year's sales and finally set the current year's sales to zero for every record in the catalog master file.
CATLGMST - Catalog Master File After UPDATE
CATLG#
CYSLS$
LYSLS$
L2SLS$
12-448-EX2
0.00
4879.52
7894.22
44-ESD-148
0.00
278.79
1489.77
A1-1258-12
0.00
1763.48
1589.44
55-78W-12A
0.00
2248.97
789.88
31-WE8-AW8
0.00
879.66
444.48
27-222-158
0.00
148.79
0.00
P7-4W8-789
0.00
3915.01
3879.11
2W-877-WW2
0.00
500.00
2489.45
2W-877-XX1
0.00
4987.88
7265.37
36Q-456-EQ
0.00
8793.45
4276.40
Example 3: Assume that the order item file contains the following data:
ORDERITM - Order Item File Before UPDATE
OITSTS
OIORD#
OICLG#
OIOQTY
OISQTY
O
13478
12-448-EX2
200
195
C
15897
44-ESD-148
22
22
C
13478
A1-1258-12
134
144
C
12489
55-78W-12A
300
300
O
11457
31-WE8-AW8
15
0
C
14999
27-222-158
36
36
O
16678
P7-4W8-789
19
17
C
14875
2W-877-WW2
87
87
O
15155
2W-877-XX1
112
107
O
17891
36Q-456-EQ
2145
0
UPDATE ORDERITM
SET OITSTS='C'
WHERE OISQTY/OIOQTY >= .95
This statement would set the order item status to 'C' (closed) when the quantity shipped is at least 95% of the quantity ordered.
ORDERITM - Order Item File After UPDATE
OITSTS
OIORD#
OICLG#
OIOQTY
OISQTY
C
13478
12-448-EX2
200
195
C
15897
44-ESD-148
22
22
C
13478
A1-1258-12
134
144
C
12489
55-78W-12A
300
300
O
11457
31-WE8-AW8
15
0
C
14999
27-222-158
36
36
O
16678
P7-4W8-789
19
17
C
14875
2W-877-WW2
87
87
C
15155
2W-877-XX1
112
107
O
17891
36Q-456-EQ
2145
0
You will note that order number 16678 did not close because the quantity shipped was only 89.5% of the order quantity.
INSERT Statement
The INSERT statement is used to add records to a database file. There are two methods of inserting data: 1) one record at a time and 2) multiple records via a SELECT statement. Also, you can specify which fields are to be loaded with values and which are to be set with default values.
The syntax for the INSERT statement is as follows:
INSERT INTO <[library/] file> [(,...)]
VALUES (,...) |