Fujitsu Computer Accessories J2X0 1634 01EN User Manual

J2X0-1634-01EN  
SymfoWARE(R)Server SQL Beginner's Guide  
How to Use This Manual  
This manual is intended as reference material for users developing application programs using SymfoWARE/RDB.  
First-time users of SymfoWARE/RDB should read the RDB User's Guide: Database Definition and the RDB User's  
Guide: Application Program Development before reading this manual. These manuals provide readers with an  
overview of SymfoWARE/RDB databases and the functions of SQL statements. These manuals also provide an  
overview on specifying SQL statements and developing application programs. For an explanation on how to develop  
application programs using SymfoWARE Programmer's Kit, refer to the Esql Programmer's Guide.  
This manual uses concrete examples to explain many kinds of data manipulation using SQL statements. Readers do  
not need to master the entire contents of this manual all at once. Instead, read through the manual to gain an  
overview of the kinds of processing that can be performed using SQL statements. Then, reread the parts of this  
manual related to writing application programs. In explanations of the syntax of SQL statements, this manual  
emphasizes ease of understanding rather than giving precise details about the syntax. For details about the syntax of  
SQL statements, refer to the SQL Reference Guide.  
Examples of specifying SQL statements given in this manual are written in C unless otherwise specified. Keywords in  
the explanations appear in Gothic type. In addition, standard terms are added to the descriptions of SQL statements.  
Refer to these terms when referring to the reference manual.  
Related manuals  
The table below lists related manuals and their informal names used in this manual.Informal name in this manual  
SymfoWARE Server  
· No corresponding manual  
ii  
SymfoWARE Programmer's Kit  
Position of this manual  
The manual system and the position of this manual are as follows:  
iii  
SymfoWARE Server  
In addition to the preceding manuals, SymfoWARE/RDB provides an online manual.  
Displaying Command References  
UNIX  
The man command is used to display the syntax of RDB commands.  
For details on the man command, refer to AnswerBook2 of the Reference Manual Collection.  
The copyright of the online manual is the property of UNIX System Laboratories, Inc. and Fujitsu Limited. Follow the  
items in the written contract to use this product properly  
Windows NT/2000/XP  
See the online help of Windows NT/2000/XP  
iv  
Action in response to displayed messages  
UNIX  
The rdbprtmsg command (RDB command) gives the meaning and user response for each displayed message.  
Windows NT/2000/XP  
See the online help of Windows NT/2000/XP.  
SymfoWARE Programmer's Kit  
Related manuals  
The related manuals are as follows:  
· Reference Manuals Collection of AnswerBook 2  
· Fujitsu COBOL User's Guide for Windows  
· COBOL85 User's Guide  
· Fujitsu COBOL Language Reference  
Precautions  
Applicable products  
UNIX  
· SymfoWARE Server Enterprise Edition 5.0 or later  
· SymfoWARE Server Hot-Standby Options 5.0 or later  
Windows NT, Windows 2000, or Windows XP  
· SymfoWARE Server Enterprise Edition V5.0 L10 or later  
SymfoWARE Programmer's Kit  
· SymfoWARE Programmer's Kit V5.0 L10  
Operating systems supporting SymfoWARE/RDB  
· Solaris  
· Microsoft(R) Windows NT(R) Server, Enterprise Edition  
· Microsoft(R) Windows NT(R) Server network operating system  
· Microsoft(R) Windows NT(R) Workstation operating system  
· Microsoft(R) Windows NT(R) Server, Terminal Server Edition  
· Microsoft(R) Windows(R) 2000 Professional operating system  
· Microsoft(R) Windows(R) 2000 Server operating system  
· Microsoft(R) Windows(R) 2000 Advanced Server operating system  
v
· Microsoft(R) Windows XP Professional  
Operating systems supporting SymfoWARE Programmer's Kit  
· Microsoft(R) Windows(R) 95 operating system  
· Microsoft(R) Windows(R) 98 operating system  
· Microsoft(R) Windows(R) 98 Second Edition  
· Microsoft(R) Windows(R) Millennium Edition  
· Microsoft(R) Windows XP Professional  
· Microsoft(R) Windows XP Home Edition  
· Microsoft(R) Windows NT(R) Server, Enterprise Edition  
· Microsoft(R) Windows NT(R) Server network operating system  
· Microsoft(R) Windows NT(R) Workstation operating system  
· Microsoft(R) Windows NT(R) Server, Terminal Server Edition  
· Microsoft(R) Windows(R) 2000 Professional operating system  
· Microsoft(R) Windows(R) 2000 Server operating system  
· Microsoft(R) Windows(R) 2000 Advanced Server operating system  
UNIX release version  
This system conforms to UNIX System V Rel 4.2MP.  
Print examples in this manual  
Althouth printed examples of SymfoWARE/RDB in this manual are not complete, readers can figure out the image of  
printed example.  
Explanatory models  
Retail store stock management databases are the main models for the databases used in the examples in this manual.  
The data in these databases is fictitious.  
Abbreviations  
The table below lists the abbreviations used in this manual:  
vi  
July 2002  
Microsoft, MS, MS-DOS, Windows, and Windows NT are trademarks or registered trademarks of Microsoft  
Corporation in the United States and other countries.  
vii  
UNIX is a registered trademark in the United States and other countries, licensed exclusively through X/Open  
Company Limited.  
Solaris is a trademark of Sun Microsystems, Inc in the United States.  
Lotus is a registered trademark of Lotus Development Corporation.  
SymfoWARE is a registered trademark of Fujitsu Limited.  
Other company and product names in this manual are trademarks or registered trademarks of their respective  
companies.  
The (R) and TM symbols have been omitted in this manual.  
All Rights Reserved, Copyright (C) FUJITSU LIMITED 2002  
viii  
Chapter 1 Overview of SQL  
This chapter describes the types of SQL and the rules common to SQL statements in this manual. This chapter  
contains the following sections:  
1.1 Types of SQL  
1.2 Common SQL Statement Rules  
1.1 Types of SQL  
SymfoWARE/RDB uses the international standard SQL for data manipulation, which is linked to languages such as C  
language and COBOL. SQL is a database language used for defining and accessing data bases.  
The following Figure: Types of SQL shows the types of SQL for SymfoWARE/RDB:  
1
[Figure: Types of SQL]  
This manual explains how to use data manipulation SQL statements to write application programs for manipulating  
data and how to use session management SQL statements, required to manipulate data.  
2
1.2 Common SQL Statement Rules  
This section explains the rules common to SQL statements in this manual.  
1.2.1 Names specified in SQL statements  
The following names can be specified in SQL statements:  
a. Schema names  
b. Table names  
c. Column names  
d. Routine names  
e. Parameter names  
f. Trigger names  
g. Host identifiers names  
h. SQL variable names  
i. Descriptor names  
j. Connection names  
k. SQL server names  
l. Cursor names  
m. Correlation names  
n. SQL statement identifiers  
o. Sequence names  
The names indicated by a to c above are defined in the database. These names are determined when schemas and  
tables are designed. The names indicated by d are routine names defined in the procedure routine definition or  
function routine definition. The names indicated by e are the parameter names defined in the procedure routine  
definition. Specify these names as is in SQL statements. The names indicated by f are the trigger names defined in  
the trigger definition. For information about database definitions, procedure routine definitions, and trigger definitions,  
see the "RDB User's Guide: Database Definition."  
The names indicated by g are host variable names. Host variables are named in the embedded SQL declaration  
clause and used in SQL statements. Host variable names are defined in C and COBOL85. These names must meet  
the following conditions:  
· Names must not begin with SQL.  
For information on the data type of host variables, see Appendix A "SQL Data Types and Equivalent Host Variable  
Data Types".  
The names in h) are defined with compound statements consisting of procedure routine definitions.  
The names in i) to n) are assigned in SQL statements. The data type for the names in i), j), and k) must be  
character-string type. The rules for assigning the names in l), m), and n) are as follows:  
· Names must start with an alphanumeric character.  
· The second and subsequent characters must be alphanumeric or numeric or an underline.  
· Lowercase characters are converted to uppercase characters.  
· Names can be up to 18 characters long.  
1.2.2 Data base used in examples  
In this section, an inventory management data base for a retail store is used as an example of a data base. The same  
data base is used throughout this manual in explanations of data manipulations. This data base contains the following  
three tables used in running inventory management applications for the retail store.  
STOCK table:  
This table contains information about products handled and stock quantities.  
ORDER table:  
This table contains information about products, the quantities of products ordered, and the prices of products  
by supplier.  
COMPANY table:  
This table contains information about names, telephone numbers, and addresses of customer companies.  
These tables are contained in a schema called STOCKS.  
STOCK table  
Figure: Inventory management data base a) shows the contents of the STOCK table. The STOCK table contains the  
following four columns:  
3
ITMNO:  
This column lists the code numbers assigned to the products.  
PRODUCT:  
This column lists the product names.  
STOCKQTY:  
This column lists the quantities of stock for the products.  
WHCODE:  
This column lists the numbers of the warehouses in which the products are stored.  
ORDER table  
Figure: Inventory management data base b) shows the contents of the ORDER table. The ORDER table contains the  
following four columns:  
CUSTOMER:  
This column lists the customers' company numbers.  
PRODNO:  
This column lists the code numbers assigned to the products.  
PRICE:  
This column lists the prices of the products.  
ORDERQTY:  
This column lists the quantities of products ordered.  
COMPANY table  
Figure: Inventory management data base c) shows the contents of the COMPANY table. The COMPANY table  
contains the following four columns:  
COMPNO:  
This column lists the code numbers assigned to the companies.  
COMPANY:  
This column lists the names of the companies.  
PHONE:  
This column lists the telephone numbers of the companies.  
ADDRESS:  
This column lists the addresses of the companies.  
In this manual, unless otherwise stated, when the preceding table or column names are used in examples of SQL  
statements, the tables and columns in Figure: Inventory management data base are specified. Refer to Figure:  
Inventory management data base when necessary. Fictitious data is used in the tables in Figure: Inventory  
management data base.  
4
[Figure: Inventory management data base]  
Attributes of table columns in inventory management data base  
Table: Attributes of table columns in the inventory management data base lists the attributes of table columns in the  
inventory management data base.  
5
[Table: Attributes of table columns in the inventory management data base]  
Relationship between STOCK table, ORDER table, and COMPANY table  
Figure: Relationship between STOCK table, ORDER table, and COMPANY table shows the relationship between the  
three tables. The STOCK table and the ORDER table are joined through ITMNO and PRODNO. The ORDER table  
and the COMPANY table are joined through CUSTOMER and COMPNO. For example, the product with PRODNO  
123 in the STOCK table is listed as REFRIGERATOR under PRODUCT. STOCKQTY of the product is 60 and the  
product is in warehouse number 1. The row for PRODNO 123 in the ORDER table provides information on PRICE and  
ORDERQTY for this product, which is 48,000 and 60, respectively. Finally, COMPNO of CUSTOMER is 61. So, from  
the row for COMPNO 61 in the COMPANY table, CUSTOMER for the product and COMPANY, PHONE, and  
ADDRESS of the company can be ascertained.  
6
[Figure: Relationship between STOCK table, ORDER table, and COMPANY table]  
7
8
Chapter 2 Retrieving Data  
This chapter describes the data manipulation that can be performed using the single row SELECT statement.  
This chapter contains the following sections:  
2.1 Fetching Column Values without Modifications  
2.2 Fetching Columns that are Undefined  
2.1 Fetching Column Values without Modifications  
Use the single row SELECT statement to fetch one row of data from a table. The following is an example of the single  
row SELECT statement:  
Example:  
In this example, STOCKQTY of the row for ITMNO 215 is obtained from the STOCK table.  
In this example, host variable STOCKQTY is specified for the select target list in the single row SELECT statement.  
Host variables are used to pass data between the application program and the database. That is, a host variable is  
specified in an SQL statement to fetch data to be accessed by the application program from the database and to store  
data specified by the application program in the database. An embedded SQL declaration clause is used to declare a  
host variable. When a host variable is specified in an SQL statement, prefix the host variable with a colon. However,  
for SQL statements that are not in C and for COBOL statements, specify host variables the same way as for general C  
and COBOL variables without a colon prefixed. For C, national-language characters cannot be used for host variable  
names.  
This section explains the following operations that can be performed by the single row SELECT statement:  
· Fetching values from multiple columns  
· Fetching data that contains a null value  
· Fetching data from multiple tables  
· Deleting duplicate rows and fetching data  
2.1.1 Fetching values from multiple columns  
In the first example in 2.1 "Fetching Column Values without Modifications", only data from column STOCKQTY was  
fetched. Suppose that data from two columns, STOCKQTY and WHCODE, is to be fetched. Obviously, the single row  
SELECT statement could be executed twice. However, executing the statement more than once increases the number  
of columns from which data is to be fetched and processing efficiency deteriorates. Instead, the single row SELECT  
statement can be executed once to fetch values from multiple columns.  
When fetching data from multiple columns, separate the column names in the select column list by commas. Specify  
an equal number of host variables for storing the fetched data. Data from fetched columns is stored in host variables  
in the same sequence from the left. Making the host variable names the same as the column names makes the  
application program more understandable. However, note that values are not stored in the host variables with the  
same names.  
In the following example multiple columns are specified in a single row SELECT statement.  
Example 1:  
In this example, values for STOCKQTY and WHCODE for the product with ITMNO "215" are obtained  
from the STOCK table. The values obtained for STOCKQTY and WHCODE are stored in host variables  
STOCKQTY and WHCODE, respectively.  
9
[Figure: Example of specifying multiple columns in a single row SELECT statement]  
If all columns in a table are to be specified in the order in which they were defined, an asterisk can be used to specify  
the columns instead of column names. In the following example an asterisk is used to specify columns:  
Example 2:  
In this example, an asterisk is used to fetch data from all columns in the STOCK table.  
SELECT * INTO :ITMNO, :PRODUCT, :STOCKQTY, :WHCODE  
FROM STOCKS. STOCK WHERE ITMNO = 215  
In Example 2, the specification of the columns from which data is to be fetched is equivalent to the four columns  
ITMNO, PRODUCT, STOCKQTY, and WHCODE being specified separately. Therefore, four host variables must be  
specified for storing data fetched from columns.  
If an asterisk is specified instead of column names, columns may be added or the sequence in which columns are  
defined may be modified due to modifications to the design of the table. If this is the case, the application program  
must be modified. To prevent modifications needing to be made to the application program, specify the columns to be  
accessed and separate them by commas.  
2.1.2 Fetching data containing null values  
Fetching data containing null values was not considered in the first two examples. This section explains how to fetch  
data containing null values.  
The variable used to indicate whether a data value is a null value is called the indicator variable. The indicator variable  
is paired with a host variable and specified in an SQL statement. When fetching data to be accessed by the  
10  
application program from the database, specify in the indicator variable whether the data to be fetched contains null  
values. When storing data specified by the application program in a database, also specify in the indicator variable  
whether the data to be stored contains null values. Use an embedded SQL declaration clause to declare the indicator  
variable. The declaration format is the same as that for a host variable. When an indicator variable is specified in an  
SQL statement, prefix the indicator variable with a colon. However, for indicator variables for C and COBOL  
statements, specify indicator variables the same way as for general C and COBOL variables without a colon prefixed.  
For C, national-language characters cannot be used for indicator variable names. The data type for indicator variables  
must be a data type equivalent to SMALLINT. See Appendix A "SQL Data Types and Equivalent Host Variable Data  
Types", for the correspondence between data types and SQL types for each language.  
The following is an example of specifying indicator variables:  
Example:  
In this example, the values for STOCKQTY and WHCODE for the product with ITMNO "215" are  
obtained from the STOCK table.  
SELECT STOCKQTY, WHCODE INTO :STOCKQTY INDICATOR :STOCKQTY_INDICATOR,  
:WHCODE INDICATOR :WHCODE_INDICATOR  
FROM STOCKS. STOCK WHERE ITMNO = 215  
In this example, the indicator variables in the single row SELECT statement are STOCKQTY_INDICATOR and  
WHCODE_INDICATOR. These indicators correspond to STOCKQTY and WHCODE, respectively. Indicator variables  
need only be specified as required. For example, an indicator variable can be specified for WHCODE without one  
being specified for STOCKQTY.  
Table: Column values and values stored in indicator variables lists the values stored in indicator variables based on  
the values for fetched columns. The length of the value for the fetched column is L and the length of the receiving  
indicator variable is M.  
[Table: Column values and values stored in indicator variables]  
11  
If fetched data has a null value and no indicator variable is specified, processing for the single row SELECT statement  
ends in an error. If it is not known whether values for a column contain a null value, specify indicator variables.  
2.1.3 Fetching data from multiple tables  
A single row SELECT statement can be used to join two or more tables to fetch data. An example of joining two tables  
follows. This section only explains the kinds of data that can be fetched as a result of joining tables. For information on  
the rules for joining and manipulating tables, see Chapter 5 "Joining Multiple Tables and Manipulating Data".  
Example:  
In this example, STOCKQTY and warehouse location of the product with ITMNO "215" are obtained  
from the STOCK table. Data about the warehouse location is contained in the WAREHOUSE table.  
EQUIPMENT is the schema containing the WAREHOUSE table.  
SELECT STOCKQTY, LOCATION INTO :STOCKQTY, :LOCATION  
FROM STOCKS. STOCK, EQUIPMENT. WAREHOUSE  
WHERE ITMNO = 215 AND STOCK. WHCODE = WAREHOUSE. WHCODE  
[Figure: Example of joining two tables to fetch data]  
2.1.4 Deleting duplicate rows and fetching data  
A single row SELECT statement can be used to fetch only one row. An error occurs if a search condition specified in a  
single row SELECT statement is true for more than one row. An error also occurs if search conditions are not specified.  
If search conditions are not specified, data in all rows of the table is fetched. However, if when multiple rows are  
fetched the values in all rows for all columns fetched are equal, the result can be placed in one row and fetched. To do  
so, specify "DISTINCT". The following is an example of specifying DISTINCT:  
Example 1  
In this example, PRODUCT and WHCODE are obtained from the STOCK table for products for which  
ITMNO is less than "120". The rows for which these values are the same are processed as one row.  
SELECT DISTINCT PRODUCT, WHCODE INTO :PRODUCT, :WHCODE  
12  
FROM STOCKS. STOCK WHERE ITMNO < 120  
[Figure: Example of deleting duplicate rows and fetching data]  
DISTINCT is used to form one row from fetched rows containing equal values. DISTINCT can easily be used  
incorrectly if the user does not realize that multiple rows will be fetched, which will result in an error. The following is  
an example of using DISTINCT incorrectly:  
Example 2:  
In this example, DISTINCT is used incorrectly. PRODUCT and STOCKQTY are obtained from the  
STOCK table for products for which ITMNO is less than "120".  
SELECT DISTINCT PRODUCT, STOCKQTY INTO :PRODUCT, :STOCKQTY  
FROM STOCKS. STOCK WHERE ITMNO < 120  
13  
[Figure: Example of specifying DISTINCT incorrectly]  
2.2 Fetching Columns that are Undefined  
This section explains the following operations that can be performed by the single row SELECT statement:  
· Performing arithmetic operations on data  
· Obtaining the total, average, maximum, and minimum values, and the row count for column values  
2.2.1 Performing arithmetic operations on data  
So far, this manual has only explained the fetching of column values from tables without modification. However, a  
single row SELECT statement can also be used to fetch the results of arithmetic operations performed on column  
values. This is achieved by specifying an operational expression in a select column list. The following is an example of  
specifying an operational expression in a single row SELECT statement:  
Example:  
In this example, the result of adding 10 to STOCKQTY for the product with ITMNO "215" is fetched  
from the STOCK table.  
SELECT STOCKQTY + 10 INTO :STOCKQTY  
FROM STOCKS. STOCK WHERE ITMNO = 215  
The "+" operator is used for addition, the "-" operator is used for subtraction, the "*" operator is used for multiplication,  
and the "/" operator is used for division. Also, a literal can be used to specify "10" as the value to be added to  
STOCKQTY. Operations can be performed on host variables and among columns as well as on columns and literals.  
When the single row SELECT statement in the preceding example is executed, "15" is stored in host variable  
STOCKQTY.  
Specifying an operational expression  
Monadic and dyadic operators can be used in operational expressions.  
Monadic operators  
The following are the two monadic operators:  
+:  
No effect  
-:  
Sign inversion  
14  
Dyadic operators  
The following are the four dyadic operators:  
+:  
Addition  
-:  
Subtraction  
*:  
Multiplication  
/:  
Division  
Table: Method of specifying and meaning of operational expressions shows the method of specifying, and the  
meaning of, operational expressions.  
[Table: Method of specifying and meaning of operational expressions]  
Specify a column name, host variable, or literal in the operator. Alternatively, the result of the operational expression  
can be specified as an operand. However, if an operational expression is specified in the operand of a monadic  
operator, the operational expression must be enclosed in parentheses.  
Priority of operations  
The priority of operations is as follows:  
1. Monadic operations  
2. Multiplication and division  
3. Addition and subtraction  
Parentheses can also be used to specify priority. An expression within parentheses is executed before any other  
operation.  
Data types for results of operational expressions  
This section explains the relationship between the data type for the result and the data type for the operand.  
Data type for results of monadic operators  
The data type for the results of monadic operators is the data type for the operand.  
Data types for results of dyadic operators  
Table: Data type for results of dyadic operators lists the data types for the results of dyadic operators. When more than  
one operator is specified in an operational expression, the data type for the results is determined for each operator.  
Follow the priority sequence for operators and apply the rules in Table: Data type for results of dyadic operators to all  
the operators in the operational expression to obtain the final data type for the operational expression.  
15  
[Table: Data type for results of dyadic operators]  
2.2.2 Obtaining total, average, maximum, and minimum value, and row count  
for column values  
So far, this manual has only explained the fetching of data from required columns for one specified row from a table.  
The single row SELECT statement can be used to fetch the results of totaling data from multiple rows. By totaling the  
data, the total, average, maximum, and minimum values, and the row count for the data can be calculated. This is  
achieved by specifying a set function in the select column list. The following is an example of specifying a set function:  
Example 1:  
In this example, the total of ORDERQTY is fetched from the ORDER table.  
16  
SELECT SUM (ORDERQTY) INTO :TOTALQTY FROM STOCKS. ORDER  
Various set functions are available for determining values in columns. "SUM" is used to obtain the total value, "AVG" to  
obtain the average value, "MAX" to obtain the maximum value, "MIN" to obtain the minimum value, and "COUNT" to  
obtain the row count.  
The reader has probably noticed that no WHERE clause was specified in the single row SELECT statement in  
Example 1 to specify the row to be fetched. The single row SELECT statement can be used to fetch data from only  
one row, so, in the previous examples in this manual, a condition specifying the row to be fetched was specified.  
However, in Example 1, since the total for all rows of a table was to be fetched, no condition specifying rows was  
required. Specify a WHERE clause to target only rows satisfying certain conditions for a total. When a WHERE clause  
is specified, only those rows for which the search condition is true are totaled.  
In Example 1, a set function was applied to the values in a column. Set functions can also be applied to the results of  
operational expressions. The following is an example of applying a set function to the results of an operational  
expression:  
Example 2:  
In this example, the totals of ORDERQTY and order price are obtained from the ORDER table for  
CUSTOMER 72. Order price is obtained by multiplying PRICE by ORDERQTY.  
SELECT SUM (ORDERQTY), SUM (PRICE * ORDERQTY)  
INTO :TOTALQTY, :TOTALPRICE  
FROM STOCKS. ORDER WHERE CUSTOMER = 72  
[Figure: Example of applying a set function to the results of an operational expression]  
17  
Types of set functions and how to specify them  
The following types of set function exist:  
COUNT (*) function:  
Obtains the table row count including rows that contain null values  
AVG function:  
Obtains average column value  
MAX function:  
Obtains maximum column value  
MIN function:  
Obtains minimum column value  
SUM function:  
Obtains total column values  
COUNT function:  
Obtains the table row count excluding rows that contain null values  
Except for the COUNT (*) function, set functions can be specified in two ways. Note that both methods of specifying  
the MAX or MIN function produce the same result.  
ALL:  
The set function is only applied to rows from the specified columns that do not contain null values.  
DISTINCT:  
The set function is applied to rows from the specified column that do not contain null or duplicate values.  
Functions in which ALL is specified are called ALL set functions. Functions in which DISTINCT is specified are called  
DISTINCT set functions. The default set function is ALL.  
Figure: Rows processed by ALL and DISTINCT set functions shows an example of the rows processed by the ALL  
and DISTINCT set functions.  
[Figure: Rows processed by ALL and DISTINCT set functions]  
The methods of specifying set functions are listed in Table: Methods of specifying set functions.  
18  
[Table: Methods of specifying set functions]  
Specify value expressions in arguments of ALL and DISTINCT set functions as shown in Table: Methods of specifying  
set functions. An operational expression that uses a column name can be specified in an argument.  
Data type of results of set functions  
Table: Data type of columns targeted by set functions and data type of results shows the relationship between the data  
type of columns targeted by set functions and the data type of the results.  
19  
[Table: Data type of columns targeted by set functions and data type of the results]  
20  
Chapter 3 Modifying Data  
This chapter describes how to use the INSERT statement, UPDATE statement (searched) and DELETE statement  
(searched) to modify data.  
This chapter contains the following sections:  
3.1 Adding Data to a Data Base  
3.2 Updating Data in a Data Base  
3.3 Deleting Data from a Data Base  
3.1 Adding data to a Data Base  
Use the INSERT statement to add data to a table. The following is an example of the INSERT statement:  
Example:  
In this example, the following data is added to the ORDER table: "78" for CUSTOMER, "400" for  
PRODNO, "125,000" for PRICE, and "50" for ORDERQTY.  
This chapter explains the following operations that can be performed by the INSERT statement:  
· Specifying a null value in data to be added  
· Using a default value in data to be added  
· Specifying a current date and time in data to be added  
· Adding data from another table  
3.1.1 Specifying a null value in added data  
To specify a null value in data to be added to a table, specify the keyword NULL or use an indicator variable instead of  
specifying a value in the insert value list. The following are examples of adding a null value in data:  
Example 1:  
In this example, the keyword NULL is specified. The following data is added to the ORDER table: "78"  
for CUSTOMER, "400" for PRODNO, and "50" for ORDERQTY. PRICE is specified as a null value.  
INSERT INTO STOCKS. ORDER (CUSTOMER, PRODNO, PRICE, ORDERQTY)  
VALUES (78, 400, NULL, 50)  
Example 2:  
In this example, an indicator variable is used. The data to be added is the same as in Example 1.  
INSERT INTO STOCKS. ORDER (CUSTOMER, PRODNO, PRICE, ORDERQTY)  
VALUES (78, 400, :PRICE INDICATOR :PRICE_INDICATOR, 50)  
In Example 2, "-1" is set as indicator variable PRICE_INDICATOR before the INSERT statement is executed. A value  
need not be set in host variable PRICE. In this case, the added data is a null value. However, if "0" or a positive value  
is specified as the indicator variable, then the data to be added becomes the value specified in host variable PRICE.  
21  
Therefore, if the data to be added contains a null value or some other value dependent on the processing of the  
application program, use an indicator variable. If an indicator variable is used, only one INSERT statement needs to  
be coded. However, for processing in which the data must be specified as a null value, specify the keyword "NULL" to  
simplify coding of the INSERT statement.  
Figure: Example of adding a null value shows the execution results for the INSERT statement in Examples 1 and 2.  
[Figure: Example of adding a null value]  
Another method of specifying that data to be added is a null value is to omit the relevant column from the insert  
column list and the insert value list. The following is an example of omitting the relevant column:  
Example 3:  
In this example, the column to which a null value is to be added is omitted. The data to be added is the  
same as in Example 1 and Example 2.  
INSERT INTO STOCKS. ORDER (CUSTOMER, PRODNO, ORDERQTY)  
VALUES (78, 400, 50)  
In Example 3, PRICE is not specified as a column name in the insert column list, nor is a value corresponding to  
PRICE specified in the insert value list. In this case, a null value is defined as the default value for PRICE or the  
added data is a null value because a default value was not defined. For more information on adding data for which a  
default value is used, see "3.1.2 Using default values in data to be added."  
Care must be taken to distinguish between whether the purpose of the processing for the application program is to  
add a null value or to add a default value. The default value for a column may have been changed due to changes in  
the design of the table. As a result, if the purpose of the processing is to add a null value, the methods shown in  
Examples 1 or 2 should be used.  
22  
3.1.2 Using default values in data to be added  
Default values, also referred to as fixed values, can be defined for each column in a table when the table is defined.  
The default values can be used by the INSERT statement to add data. If no value is specified for a column for which  
no default value is defined, a null value is added to the column. However, processing ends in an error if the column  
has a NOT NULL constraint.  
Setting a default value for a column in which data is to be added  
To set a default value for a column in which data is to be added, specify the keyword "DEFAULT" in the insert value list  
of the INSERT statement. In this case, the name of the column for which the default value is to be set cannot be  
omitted from the insert column list. The following is an example in which default values for two columns are set:  
Example 1:  
In this example, a row of data is added to the ORDER table. The data to be added is as follows: "78"  
for CUSTOMER and "400" for PRODNO. PRICE and ORDERQTY are specified with the default values  
defined for the columns.  
[Figure: Adding data using default values]  
To set a default value for a column in which data is to be added, instead of specifying the keyword "DEFAULT", omit  
the value from the insert value list. In this case, also omit the name of the column for which the default value is to be  
set from the insert column list. The following is an example in which the insert value list and the insert column list for  
the INSERT statement in Example 1 are omitted:  
Example 2:  
In this example, the value to be added and the column to which it is to be added are omitted. The data  
added is the same as in Example 1.  
23  
Specifying null values for all columns in data to be added  
To set null values in all columns in data to be added, specify the keyword "DEFAULT VALUES" instead of specifying  
an insert column list or an insert value list. The following is an example in which DEFAULT VALUES is specified:  
Example 3:  
In this example, a row of data is added to the ORDER table. All the columns for the data to be added  
are specified with the default value defined for each column.  
INSERT INTO STOCKS. ORDER DEFAULT VALUES  
[Figure: Adding data in which default values are used for all columns]  
3.1.3 Setting the current date and time in data to be added  
To set the current date and time in the data to be added, specify the appropriate keyword in the insert value list of the  
INSERT statement. To set the current date, specify CURRENT_DATE. To set the current time, specify  
CURRENT_TIME. To set the current time-stamp, specify CURRENT_TIMESTAMP. An example of setting the current  
time in the data to be added is given below.  
The example uses the DAILY_ORDER table, which contains the orders of a single day. The table consists of four  
columns: CUSTOMER, PRODNO, ORDERQTY, and PROCESS_TIME.  
Example:  
In this example, a row of data is added to the DAILY_ORDER table. The value "61" is specified for  
CUSTOMER, the value of host variable PRODNO is specified for PRODNO, and the value of host  
variable ORDERQTY is specified for ORDERQTY. The current time (16:30:17) is specified for  
24  
PROCESS_TIME. In this example, "212" has been specified for host variable PRODNO, and "15" has  
been specified for host variable ORDERQTY.  
INSERT INTO STOCKS.DAILY_ORDER (CUSTOMER, PRODNO, ORDERQTY, PROCESS_TIME)  
VALUES (61, :PRODNO, :ORDERQTY, CURRENT_TIME)  
[Figure: Adding data using the current time]  
3.1.4 Adding data from another table  
Depending on the processing performed by the application program, data may need to be fetched from one table and  
added to another. In such cases, processing is not very efficient if a single row SELECT statement or a cursor is used  
to fetch and add data. To improve efficiency, an INSERT statement can be used to add data from one table to another  
table. Use a query specification to fetch data to be added from a table. The following is an example of a query  
specification:  
Example 1:  
In this example, the row for PRODUCT TELEVISION is added to table STOCK2 table from the STOCK  
table.  
25  
[Figure: Using a query specification to add data (where the column layout is the same)]  
In Example 1, the layout of the columns in the table to which data is added and the table from which data is fetched  
have a one-to-one correspondence. Usually, the layout of columns in tables does not correspond so closely. For  
example, the data from some columns in a table may be added to another table. Or, a value determined beforehand  
by an application program may be added to a column in the table to which data is to be added. An example of this  
follows.  
Suppose a table named CASSETTE table containing STOCKQTY and ORDERQTY data for CASSETTE DECK and  
CASSETTE TAPE. This table consists of four columns: ITMNO, TYPE, QUANTITY, and PART. CASSETTE DECK is  
set as "D" and CASSETTE TAPE as "T" in column TYPE. The quantity of stock and orders is set in column QUANTITY.  
In column PART, stock is "1", orders placed with CUSTOMER is "2", and orders placed with multiple CUSTOMER is  
"3".  
Example 2:  
In this example, ITMNO and STOCKQTY from the STOCK table are added to the CASSETTE table.  
Also, "D", which stands for CASSETTE DECK, is set in column TYPE, and "1", which stands for stock,  
is set in host variable PART.  
26  
[Figure: Using a query specification to add data (when column layout does not correspond)]  
Besides fetching specific values from a table, query specifications can also be used to join and manipulate multiple  
tables. Query specifications can also be used to group tables and total their values. An example of this follows. For  
detailed information, see Chapter 5 "Joining Multiple tables and Manipulating Data" and 6.1 "Grouping tables and  
Manipulating Data". Do not be concerned too much about the details of Figure: Adding data using a complex query  
specification, just try to understand in general terms what happens when a query specification is used.  
Example 3:  
In this example, PRODNO for the ORDER table and the total of ORDERQTY for each PRODNO is  
added to the CASSETTE table. The data to be added is specified as only for orders by more than one  
CUSTOMER. In addition, "D", which stands for CASSETTE DECK, is set in column TYPE, and "3",  
which stands for orders by more than one CUSTOMER, is set in host variable PART.  
27  
[Figure: Adding data using a complex query specification]  
28  
3.2 Updating data in a Data Base  
Use the UPDATE statement to update data in a table. The following is an example of the UPDATE statement:  
Example:  
In this example, ORDERQTY is updated to "50" for the data for PRODNO "215" and CUSTOMER "61"  
in the ORDER table.  
This section explains the following operations that can be performed by the UPDATE statement:  
· Updating values in multiple columns  
· Specifying a null value for data to be updated  
· Using default values in data to be updated  
· Specifying the current date and time in data to be updated  
· Using values from other columns in data to be updated  
· Performing arithmetic operations on data to be updated  
· Updating all rows in a table  
29  
Data can also be updated using a cursor. For information on this method, see 4.5 "Updating Data Using a Cursor".  
The UPDATE statement explained in this section is called the UPDATE statement (searched) because search  
conditions are used to specify rows to be updated.  
3.2.1 Updating values in multiple columns  
In the first example in 3.2 "Updating Data in a Data Base", data was updated in column ORDERQTY only. Suppose  
that data in two columns, PRICE and ORDERQTY, is to be updated. An UPDATE statement could be executed once  
for each column, but this is not efficient. Instead, the UPDATE statement can update values in multiple columns at the  
same time.  
To update data in multiple columns, specify the columns by separating each set clause with a comma. The values for  
columns not specified in the set clause are not changed. The following is an example of updating multiple columns:  
Example:  
In this example, PRICE is updated to "216,000" and ORDERQTY is updated to "160" for the data for  
CUSTOMER "61" and PRODNO "215" in the ORDER table.  
UPDATE STOCKS. ORDER SET PRICE = 216000, ORDERQTY = 160  
WHERE CUSTOMER = 61 AND PRODNO = 215  
[Figure: Example of updating multiple columns]  
3.2.2 Specifying a null value for data to be updated  
To update data in a table to a null value, specify the keyword "NULL" instead of specifying a value in the set clause or  
use an indicator variable. The following is an example of updating data to a null value:  
Example 1:  
In this example, the keyword "NULL" is specified. PRICE is updated to "216,000" and ORDERQTY is  
updated to a null value for data for CUSTOMER "61" and PRODNO "215" in the ORDER table.  
30  
UPDATE STOCKS. ORDER SET PRICE = 216000, ORDERQTY = NULL  
WHERE CUSTOMER = 61 AND PRODNO = 215  
Example 2:  
In this example, an indicator variable is used. The data to be updated is the same as in Example 1.  
In Example 2, "-1" is set for indicator variable ORDERQTY_INDICATOR, then the UPDATE statement is executed. A  
value need not be specified in host variable ORDERQTY. In this case, the data to be updated becomes a null value. If  
"0" or a positive value is set for the indicator variable, the data to be updated becomes the value specified in host  
variable ORDERQTY.  
Therefore, if the data to be updated is a null value or some other value depending on the processing for the  
application program, use an indicator variable. If an indicator variable is used, only one UPDATE statement needs to  
be coded. However, in processing in which the data must be specified as a null value, specify the keyword "NULL" to  
simplify the coding of the UPDATE statement.  
Figure: Example in which data is updated to a null value shows the execution results for the UPDATE statements in  
Examples 1 and 2.  
[Figure: Example in which data is updated to a null value]  
3.2.3 Using default values in data to be updated  
Adding data using default values is explained in 3.1.2 "Using default values in data to be added". The UPDATE  
statement can also be used to update data using default values. To set a default value in data to be updated, specify  
the keyword DEFAULT instead of specifying values in a set clause. If no default value is defined for the column  
specified in the set clause, the data in the column is updated to a null value. However, this processing ends in an error  
if a column with a NOT NULL constraint exists. The following is an example of specifying the keyword DEFAULT:  
Example:  
In this example, the keyword "DEFAULT" is specified. ORDERQTY is updated to the default value for  
the data for PRODNO "240" in the ORDER table.  
31  
UPDATE STOCKS. ORDER SET ORDERQTY = DEFAULT WHERE PRODNO = 240  
[Figure: Updating data using a default value]  
3.2.4 Specifying the current date and time in data to be updated  
To specify the current date and time in data to be added, specify the appropriate keyword in the set clause instead of a  
value. To specify the current date, specify CURRENT_DATE. To specify the current time, specify CURRENT_TIME. To  
specify the current time-stamp, specify CURRENT_TIMESTAMP. An example of specifying the current time-stamp in  
data to be updated is given below.  
The example uses the TV_STOCK table, which consists of three columns: PROCESS_DATETIME, ITMNO, and  
STOCKQTY. STOCKQTY at PROCESS_DATETIME is specified for STOCKQTY.  
Example:  
In this example, PROCESS_DATETIME is updated to the current date and time (October 14, 2000,  
18:35:31) and STOCKQTY is updated to the value of host variable STOCKQTY.  
PROCESS_DATETIME and STOCKQTY are updated for the data for ITMNO "212" in the TV_STOCK  
table. The value "65" has been specified for host variable STOCKQTY.  
32  
[Figure: Updating data using the current date and time]  
3.2.5 Using values from other columns in data to be updated  
Values from other columns can be used to update data. An example of this follows.  
Suppose that a table named SHIPMT table consisting of the four columns ITMNO, SHIPQTY, PREVSHIPMT, and  
VARIATION exists. When products are shipped, the quantity is set in SHIPQTY and the quantity previously shipped is  
set in PREVSHIPMT. VARIATION is the difference between the current SHIPQTY and the previous SHIPQTY. The  
value of VARIATION is not set every time shipment data is produced. Instead, data is assembled and set only when  
required.  
Example:  
SHIPQTY is updated to "120" for the product with ITMNO "140" in the SHIPMT table, and the previous  
SHIPQTY is set for PREVSHIPMT. Column VARIATION is not updated at this time.  
UPDATE STOCKS. SHIPMT SET SHIPQTY = 120, PREVSHIPMT = SHIPQTY  
WHERE ITMNO = 140  
33  
[Figure: Example of using values from other columns to update data]  
Note that when column values are specified for data to be updated, the values used are those prior to execution of the  
UPDATE statement. In this example, SHIPQTY is updated to "120", as specified by the literal. However,  
PREVSHIPMT is updated to "100", the value of SHIPQTY prior to execution of the UPDATE statement. SHIPQTY is  
not "120", which is the value to be updated to by this UPDATE statement.  
3.2.6 Performing arithmetic operations on data to be updated  
The result of arithmetic operations performed on values such as column values can be used for data to be updated.  
The following is an example of using this for data to be updated:  
Example:  
In this example, PRICE is reduced by 10% and 150 is added to ORDERQTY for the data for  
CUSTOMER "61" in the ORDER table.  
[Figure: Example in which arithmetic operations are performed on data to be updated]  
In this example, the operation "PRICE x0.9" is performed to reduce PRICE by 10%. The operation "PRICE -(PRICE  
x 0.1)" could also be used. However, the processing efficiency of SQL statements deteriorates as the number of  
operators increases. As in this example, simplify the operational expression as much as possible.  
34  
3.2.7 Updating all rows in a table  
To update all rows in a table, do not specify any search conditions or the keyword "WHERE".  
Review the example in Figure: Example of using values from other columns to update data. In this example, when  
shipment data was produced, the values for SHIPQTY and PREVSHIPMT were updated. However, VARIATION was  
not specified. In the following example, VARIATION is specified to be updated to the current value. The following is an  
example of specifying a value for VARIATION:  
Example:  
In this example, the value of VARIATION in all rows in the SHIPMT table is updated to the value  
derived by subtracting PREVSHIPMT from SHIPQTY.  
[Figure: Example of updating all rows in a table]  
In this example, the value of VARIATION was updated in all rows. However, for rows for which shipment data was not  
produced since the previous update, the actual values did not change. Nevertheless, data in these rows was updated.  
In other words, the value was updated to a value equal to the original value. The ratio of rows in which the actual  
value does not change may be large compared to the row count for the entire table. Therefore, using a method to  
manage rows such that only the required rows are updated, would improve processing efficiency.  
3.3 Deleting Data from a Data Base  
To delete data from a table, use the DELETE statement. The following is an example of the DELETE statement:  
Example:  
In this example, the row for CUSTOMER 61 and PRODNO 215 is deleted from the ORDER table.  
35  
This section explains the following operation that can be performed by the DELETE statement:  
· Deleting all rows from a table  
A cursor can also be used to delete data. For information on using a cursor to delete data, see 4.6 "Deleting Data  
Using a Cursor".  
The method of deleting rows explained in this section is called the "DELETE statement" (searched) because the rows  
to be deleted are specified using search conditions.  
3.3.1 Deleting all rows from a table  
To delete all rows from a table, do not specify search conditions or the keyword "WHERE". The following is an  
example of this:  
Example:  
In this example, all rows are deleted from the ORDER table.  
36  
[Figure: Example of deleting all rows from a table]  
37  
38  
Chapter 4 Using a Cursor to Manipulate Data  
This chapter describes how to use a cursor to manipulate data.  
This chapter contains the following sections:  
4.1 Cursor Overview  
4.2 Declaring a Cursor  
4.3 Opening and Closing a Cursor  
4.4 Positioning a Cursor and Fetching Data  
4.5 Updating Data Using a Cursor  
4.6 Deleting Data Using a Cursor  
4.1 Cursor Overview  
A cursor is a virtual tool used to specify one row in a table. A cursor can be used in fetching, updating, or deleting data  
in a row. Using a cursor to specify a row is called positioning the cursor.  
A cursor is either in an open or closed state. If a cursor has never been opened, it is in a state equivalent to a closed  
state. If a cursor can be used to manipulate data, it is in an open state.  
When a cursor is in an open state, it may or may not be positioned on a specific row in a table. Understanding that a  
cursor may be considered to be in the following four positions helps provide an understanding of the cursor:  
· Immediately before the first row  
· On a row  
· Between two rows  
· Immediately after the last row  
Figure: Cursor overview provides a simple overview of the cursor using an abbreviated the STOCK table. Column  
names have not been included in Figure: Cursor overview. The SQL statements used for various data manipulations  
are explained later in this manual.  
39  
[Figure: Cursor overview]  
40  
Sequence of data manipulations using a cursor  
Cursor SQL statements are as follows:  
· Cursor declaration (DECLARE CURSOR)  
· OPEN statement  
· CLOSE statement  
· FETCH statement  
· UPDATE statement  
· DELETE statement  
The SQL statement used to declare a cursor is the cursor declaration, a non-executable statement. The cursor  
41  
declaration specifies the tables in which data is to be manipulated and the method by which data is to be fetched.  
Before data can be manipulated using a cursor, the cursor must be declared. The cursor declaration must be coded  
before any SQL statements that use the cursor are coded, regardless of the execution sequence of the application  
program.  
Data is manipulated in the following sequence using a cursor:  
1. The OPEN statement opens the cursor.  
2. The FETCH statement positions the cursor at a specific row in the table and fetches data.  
3. The data in the row where the cursor is positioned is processed.  
- Use an application program to process fetched data.  
- Use the UPDATE statement to update values in the row where the cursor is positioned.  
- Use the DELETE statement to delete the row where the cursor is positioned.  
4. The CLOSE statement closes the cursor.  
To process more than one row, repeat steps 2) and 3). These steps are illustrated in the flowchart in Figure: Flowchart  
of using cursor to manipulation data.  
[Figure: Flowchart of using cursor to manipulation data]  
Figure: Example of an application program that uses a cursor to manipulate data is an example of an application  
program that uses a cursor to manipulate data. The application program processes the STOCK table as follows:  
· Deletes rows in which STOCKQTY is "0"  
· Changes WHCODE to "5" in rows for which PRODUCT is REFRIGERATOR.  
42  
· Calculates the total of STOCKQTY in the rows for which PRODUCT is REFRIGERATOR and stores the value  
of total in variable TOTAL.  
[Figure: Example of an application program that uses a cursor to manipulate data]  
43  
4.2 Declaring a Cursor  
Before data can be manipulated using a cursor, the cursor must be declared using a cursor declaration, a  
non-executable statement. The cursor declaration must be coded before any SQL statements that use the cursor are  
coded, regardless of the execution sequence of the application program.  
The following is an example of a cursor declaration:  
Example 1:  
In this example, a cursor to obtain CUSTOMER, PRODNO, and ORDERQTY from the ORDER table is  
declared.  
This cursor declaration declares a cursor to fetch values from all rows of the three columns CUSTOMER, PRODNO,  
and ORDERQTY in the ORDER table.  
The tables to be manipulated by a cursor and the method of fetching data are specified in the query expression of the  
cursor declaration. Example 1 is a simple example of data manipulation performed by a query expression. In contrast,  
a cursor can be used to fetch data from tables and perform various processes according to the application program  
logic. A query expression can be used to group and manipulate tables and fetch data from the aggregate of multiple  
tables. The skillful use of query expressions can greatly reduce the amount of processing required to be performed by  
application programs.  
Query expression  
Query expressions can be specified in two ways. A single query specification can be specified in a query expression.  
Alternatively, two or more query specifications, joined by UNION, can be specified in a query expression. For  
information on how to specify "UNION", see 5.5 "Obtaining the Aggregate for Rows from Multiple Tables".  
Query specification  
The format of a query specification is the same as that for a single row SELECT statement minus the INTO clause. A  
query specification derives a table in the same way as a single row SELECT statement. In both cases, values for  
columns specified in a select column list are fetched from rows of a table, derived from a FROM clause, that satisfy  
the WHERE clause conditions. However, whereas a single row SELECT statement ends in an error if more than one  
row is to be fetched, a query specification can be used to fetch any number of rows.  
The GROUP BY clause and the HAVING clause can be specified in query specifications. For details, see 6.1  
"Grouping Tables and Manipulating Data", which explains using a cursor declaration containing a GROUP BY clause  
in the query specification. The following is an example of specifying a GROUP BY clause:  
Example 2:  
In this example, a cursor is declared to obtain PRODUCT, the number of customers who have placed  
orders, and the totals of STOCKQTY and ORDERQTY for each ITMNO in the STOCK table and the  
ORDER table.  
44  
[Figure: Deriving a cursor table]  
Sequence of rows fetched using a cursor  
Note that the sequence of rows fetched using a cursor was not specified. Although the query expression specifies the  
rows to be included in the cursor table, the query expression does not specify the sequence of fetching the rows. This  
is true even for a simple cursor that fetches all rows of a table. The rows in the cursor table shown in Figure: Deriving  
a cursor table are sometimes arranged in ITMNO sequence. However, when an application program is actually  
executed, the sequence of rows may be as shown in Figure: Example of rows in a cursor table when the sequence is  
undefined.  
45  
[Figure: Example of rows in a cursor table when the sequence is undefined]  
Reordering the sequence of rows  
A cursor declaration can specify the reordering, or sorting, of rows in a cursor table. This is done by specifying the  
ORDER BY clause after the query expression. The following is an example of specifying the ORDER BY clause:  
Example 3:  
In this example, the cursor declaration used in Example 2 specifies that data is fetched by the cursor in  
the sequence from lowest ITMNO.  
Specify the names of the columns used for sorting and the method of sorting in the ORDER BY clause. The columns  
used for sorting must be columns contained in the sort table. In other words, only columns derived from the result of  
the query expression can be used in sorting. Columns used in sorting are called sort keys. Sorting can be performed  
in either ascending (ASC) or descending (DESC) order. If no sort method is specified, the system uses "ASC" as the  
default.  
ASC:  
This reorders values starting with the smallest value. This is called sorting in ascending order.  
DESC:  
This reorders values starting with the largest value. This is called sorting in descending order.  
46  
Figure: Deriving a cursor table shows the sequence of rows in the cursor table if the cursor declaration is specified as  
in Example 3. In Figure: Deriving a cursor table, the value of ITMNO is different for each row, so the sequence of rows  
in the cursor table is unique. The situation when the value of ITMNO is the same for more than one row is explained  
further on.  
The sort specification determines the sequence of rows that have different values for the sort key. However, if more  
than one row has the same value for the sort key, the sequence of rows cannot be determined. To reorder rows that  
have the same value for the sort key, specify multiple sort keys separated by commas. Multiple sort keys specified in  
the ORDER BY clause are called the first sort key, the second sort key, and so on. Rows in the cursor table are first  
reordered by the first sort key, then rows that have the same value for the first sort key are reordered by the second  
sort key. The sequence of rows that have the same values for all specified sort keys cannot be determined. The  
following is an example of specifying sort keys:  
Example 4:  
In this example, the cursor declaration specifies that data is fetched by cursor in the sequence from  
lowest WHCODE.  
[Figure: Example of specifying multiple sort keys]  
When rows are to be reordered, the names of columns used as sort keys must be specified. However, columns  
containing the results of operational expressions or set functions specified in a select column list in a query  
specification do not have names. To use a column without a name as a sort key, specify a column number instead of a  
column name in the sort key specification. Columns are numbered in sequence from the left in the cursor table. The  
following is an example of specifying column numbers:  
Example 5:  
47  
In this example, the cursor declaration used in Example 2 is used. Here, data is fetched by the cursor  
using the number of CUSTOMERs as the first sort key. The data is sorted in descending order. The  
totals of STOCKQTY and ORDERQTY are used as the second sort key, and the data is sorted in  
ascending order.  
[Figure: Example of specifying column numbers in sort specification]  
Updatable cursors and read-only cursors  
Two types of cursors are available: updatable cursor and read-only cursors. Only updatable cursors can be used in  
UPDATE statements and DELETE statements. Both updatable cursors and read-only cursors can be used in FETCH  
statements. The cursor declaration determines if a cursor is updatable or read-only.  
The following shows cursor declaration conditions for read-only cursors. SCM is used as the schema name for tables  
TBL1 and TBL2 in this example.  
48  
If one of conditions a) to t) applies to the format of the cursor declaration, the cursor is read-only. If none of the  
conditions applies, the cursor is updatable.  
Conditions a) to p) are specified in query specifications. Condition q) is specified in query expressions. Conditions r) to  
t) are specified in cursor declarations. Conditions r) and s) support updatable cursors only if FOR UPDATE is specified  
in the updatable clause. For more information on the subquery in condition p), see 6.2 "Specifying Various Search  
Conditions". For more information on UNION in condition q), see 5.5 "Obtaining the Aggregate for Rows from Multiple  
Tables".  
49  
4.3 Opening and Closing a Cursor  
Before data can be manipulated using a cursor, the cursor must be opened using an OPEN statement. After data has  
been manipulated, the cursor must be closed using a CLOSE statement.  
Opening a cursor  
Use an OPEN statement to begin using a cursor. The cursor table specified in the cursor declaration is created as  
soon as the OPEN statement is executed. The following is an example in which STOCK_CSR is opened:  
The cursor declaration specifying the cursor name must be coded before the OPEN statement in the coding sequence  
for the application program. A cursor specified in an OPEN statement must be one that has never been opened or one  
that has been closed by a CLOSE statement. If a cursor is opened more than once, an error occurs. Different cursors  
can be opened at the same time.  
Closing a cursor  
Use the CLOSE statement to stop using a cursor. The following is an example of closing STOCK_CSR:  
The cursor declaration specifying the cursor name must be coded before the CLOSE statement in the coding  
sequence for the application program. A cursor specified in a CLOSE statement must be open. If a cursor that is not  
open is specified in a CLOSE statement, an error occurs.  
4.4 Positioning a Cursor and Fetching Data  
Use the FETCH statement to manipulate data in the following ways:  
· To position a cursor and fetch data from the row on which the cursor is positioned  
· To position a cursor on the row to be updated and update data in the row using the UPDATE statement  
· To position a cursor on a row and delete the row using the DELETE statement  
The following is an example of specifying the FETCH statement:  
Example 1:  
In this example, a cursor is declared to fetch ITMNO and STOCKQTY for products with WHCODE "2"  
from the STOCK table. The cursor moves one row at a time to fetch values.  
50  
Figure: Example of using a FETCH statement to position a cursor shows the results of executing the FETCH  
statement in Example 1. The cursor is positioned on the second row because the FETCH statement was executed  
twice.  
[Figure: Example of using a FETCH statement to position a cursor]  
51  
The cursor declaration specifying the cursor name must be coded before the FETCH statement in the coding  
sequence for the application program. The cursor specified by the FETCH statement must be open. If a cursor that is  
not open is specified, an error occurs. If the FETCH statement is executed when the cursor is positioned in the last  
row, the result is "No data". Likewise, if the FETCH statement is executed when the cursor is positioned immediately  
after the last row, the result is still "No data".  
Host variable in which fetched data is stored  
The host variables in which values are stored for the row in which the cursor is positioned are specified in the fetch  
target list. The number of host variables specified must be the same as the number of columns in the cursor table.  
Data from the fetched columns is stored in the host variables in the same sequence from the left.  
Fetching data containing null values  
To fetch data containing null values, specify an indicator variable to be paired with the host variable in which fetched  
data is stored. The following is an example of specifying a FETCH statement to fetch data containing null values:  
Example 2:  
In this example, indicator variables are specified in the FETCH statement used in Example 1.  
FETCH STOCK_CSR INTO :ITMNO INDICATOR :ITMNO_INDICATOR  
:STOCKQTY INDICATOR :STOCKQTY_INDICATOR  
In example 2, "ITMNO_INDICATOR" and "STOCKQTY_INDICATOR" are indicator variables. Refer to Table: Column  
values and values stored in indicator variables for details of the values stored in indicator variables by the FETCH  
statement.  
If indicator variables are not specified and fetched data contains a null value, the FETCH statement results in an error.  
Therefore, specify indicator variables if you do not know whether any of the values for columns are null values.  
Moving the cursor in different directions  
The cursor can be moved in different directions by specifying a fetch direction in the FETCH statement. When  
specifying a fetch direction, specify SCROLL in the cursor declaration. When a fetch direction is omitted, NEXT is  
assumed to be specified.  
The following are examples of specifying a fetch direction. The same cursor declaration applies to examples a) to f).  
a) This example fetches the value of the row following the current row (NEXT specified). The cursor is positioned to [5]  
in the following Figure: Example of positioning a cursor in different directions.  
b) This example fetches the value of the row preceding the current row (PRIOR specified). The cursor is positioned to  
[4] in the following Figure: Example of positioning a cursor in different directions.  
Example: FETCH PRIOR FROM CSR1 INTO :H1, :H2  
c) This example fetches the value of the first row (FIRST specified) regardless of the current row. The cursor is  
positioned to [1] in the following Figure: Example of positioning a cursor in different directions.  
Example: FETCH FIRST FROM CSR1 INTO :H1, :H2  
52  
d) This example fetches the value of the last row (LAST specified) regardless of the current row. The cursor is  
positioned to [8] in the following Figure: Example of positioning a cursor in different directions.  
Example: FETCH LAST FROM CSR1 INTO :H1, :H2  
e) This example fetches the value of the nth row from the first or last row regardless of the current row (ABSOLUTE  
specified).  
- The value of the second row from the first row is fetched. The cursor is positioned to [2] in the following Figure:  
Example of positioning a cursor in different directions.  
Example: FETCH ABSOLUTE 2 FROM CSR1 INTO :H1, :H2  
- The value of the second row from the last row is fetched. The cursor is positioned to [7] in the following Figure:  
Example of positioning a cursor in different directions.  
Example: FETCH ABSOLUTE -2 FROM CSR1 INTO :H1, :H2  
f) This example fetches the value of the nth row from the current row (RELATIVE specified).  
- The value of the second row down from the current row is fetched. The cursor is positioned to [6] in the following  
Figure: Example of positioning a cursor in different directions.  
Example: FETCH RELATIVE 2 FROM CSR1 INTO :H1, :H2  
- The value of the third row up from the current row is fetched. The cursor is positioned to [3] in the following Figure:  
Example of positioning a cursor in different directions.  
Example: FETCH RELATIVE -3 FROM CSR1 INTO :H1, :H2  
[Figure: Example of updating data using a cursor (column to be updated is in the cursor tab  
le)]  
4.5 Updating Data Using a Cursor  
Use an UPDATE statement to update data in a row in which a cursor is positioned. An updatable cursor must be used  
in an UPDATE statement. The following is an example of specifying an UPDATE statement:  
Example 1:  
In this example, STOCKQTY is updated to "150" for the row in which the cursor is positioned in Figure:  
Example of using a FETCH statement to position a cursor.  
53  
[Figure: Example of updating data using a cursor (column to be updated is not in the curso  
r table)]  
Data can also be updated using an UPDATE statement without using a cursor. For more information on this method,  
see 3.2 "Updating Data in a Data Base".  
Example 2:  
In this example, the cursor in Figure: Example of using a FETCH statement to position a cursor is  
moved again. Then ITMNO is updated to the value in the host variable ITMNO, PRODUCT is updated  
to "CD PLAYER", STOCKQTY is updated to twice its original value, and WHCODE is updated to a null  
value. Specify "340" in the host variable ITMNO.  
54  
[Figure: Example of updating data using a cursor (column to be updated is not in the curso  
r table)]  
Data can also be updated using an UPDATE statement without using a cursor. For more information on this method,  
see 3.2 "Updating Data in a Data Base".  
This method of updating data using an UPDATE statement is called UPDATE statement (positioned) because the row  
to be updated is specified by positioning the cursor.  
4.6 Deleting Data Using a Cursor  
Use the DELETE statement to delete data from a row in which a cursor is positioned. An updatable cursor must be  
specified in a DELETE statement. The following is an example of specifying the DELETE statement:  
Example:  
In this example, the row in which the cursor is positioned in Figure: Example of using a FETCH  
statement to position a cursor is deleted.  
55  
[Figure: Example of deleting data using a cursor]  
Data can also be deleted using an UPDATE statement without using a cursor. For more information on this method,  
see 3.3 "Deleting Data from a Data Base".  
This method of deleting data using a DELETE statement is called DELETE statement (positioned) because the row to  
be deleted is specified by positioning the cursor.  
56  
Chapter 5 Joining Multiple Tables and  
Manipulating data  
This chapter describes how to join multiple tables and manipulate data when data is fetched from data bases. For  
example, suppose that to check on the status of orders for products, data for CUSTOMER, PRODUCT, and  
ORDERQTY is fetched from the stock inventory data base. This data is to be fetched from the ORDER table, but data  
for PRODUCT is discovered to be unavailable there. However, PRODUCT data is available in the STOCK table. As  
shown in Figure: Relationship between STOCK table, ORDER table, and COMPANY table, the STOCK table and the  
ORDER table are related through ITMNO in the STOCK table and PRODNO in the ORDER table. Therefore, these  
two tables can be joined and data fetched.  
5.1 Deriving a New Table from Multiple Tables  
5.2 Specifying Conditions to Join Tables  
5.3 Manipulating Data Using Aliases in Tables  
5.4 Joining a Table to Itself and Manipulating Data  
5.5 Obtaining the Aggregate for Rows from Multiple Tables  
5.1 Deriving a New Table from Multiple tables  
To join multiple tables and fetch data, specify the table names, separated by commas, in the FROM clause of the table  
expression. When multiple table names are specified, a new table is derived from the result of the FROM clause. Data  
can then be fetched from the derived table. The kind of table derived when multiple table names are specified is as  
follows:  
The following is an example of fetching data from multiple tables:  
Example:  
In this example, table names TBL1 and TBL2 are specified in the FROM clause and SCM is the  
schema name for these tables.  
SELECT ... FROM SCM.TBL1, SCM.TBL2  
57  
[Figure: Table derived when two table names are specified in a FROM clause]  
If two table names are specified in a FROM clause, the table derived from the FROM clause contains all columns and  
rows in the two specified tables. This is called the expanded direct product of the table. In the same way, if three or  
more tables names are specified in the FROM clause, the derived table contains all columns and rows in the specified  
tables. In other words, the number of columns that results is the summation of the number of columns in the specified  
tables. The row count is the product of the row counts of the specified tables. The sequence of columns in the derived  
table is the sequence of columns in each table in the order in which the tables were specified.  
5.2 Specifying Conditions to Join Tables  
The expanded direct product derived from specifying multiple tables in a FROM clause combines all rows from the  
specified tables, although many of the rows may contain insignificant data. When this data is processed by an  
application program, all the rows are fetched, but the program only processes rows containing significant data.  
Because a large number of rows are fetched, processing is not very efficient. So, in addition to joining tables, data  
must be narrowed down to just significant data. To do so, specify a condition to join tables in the WHERE clause of the  
table expression. The following is an example of specifying conditions in the WHERE clause:  
Example 1:  
In this example, the data that joins the two tables TBL1 and TBL2 in the example in Figure: Table  
derived when two table names are specified in a FROM clause is specified as values for column COLA  
and column COLZ.  
SELECT ... FROM SCM.TBL1, SCM.TBL2 WHERE COLA = COLZ  
58  
[Figure: Example of specifying conditions in WHERE clause for joining tables]  
In Example 1, because the "COLA = COLZ" condition is specified in the WHERE clause, the only rows fetched are  
those in which the values of columns COLA and COLZ are the same.  
When multiple tables are specified in a FROM clause, columns with the same name may exist in more than one table.  
To specify a column name that may exist in more than one table, qualify the column name by preceding it with a table  
name and a period. For example, if the column name COLA exists in both tables TBL1 and TBL2, specify column  
COLA in table TBL1 as "TBL1.COLA" or "SCM.TBL1.COLA." Similarly, specify column COLA in table TBL2 as  
"TBL2.COLA" or "SCM.TBL2.COLA".  
As an example of joining multiple tables, the following examines the status of orders mentioned at the beginning of  
this chapter.  
Example 2:  
In this example, CUSTOMER, PRODUCT, and ORDERQTY are fetched from the ORDER table and  
the STOCK table. The column names are specified with a table name qualifier. (In reality, the ORDER  
table and the STOCK table do not contain columns with the same name, so table name qualifiers need  
not be specified.) Table name qualifiers are only required when multiple tables specified in a FROM  
clause contain columns with the same name.  
SELECT ORDER. CUSTOMER, STOCK. PRODUCT, ORDER. ORDERQTY  
FROM STOCKS. ORDER, STOCKS. STOCK  
WHERE ORDER. PRODNO = STOCK. ITMNO  
59  
[Figure: Example in which multiple tables are joined and data manipulated]  
60  
Join tables  
In Example 2, all rows in the ORDER table and STOCK table are in the table derived from the WHERE clause. Some  
rows that are not in the ORDER or STOCK table may also be present depending on the conditions. To also fetch rows  
that do not satisfy the conditions, use a join table. A join table can be used to also fetch rows which do not satisfy the  
join specification fetch conditions, but which are in the tables specified by the join type. When LEFT is coded as the  
outer join type, all rows of the table to the left of the join type are fetched. When RIGHT is coded, all rows of the table  
on the right are fetched. A specification example is given below.  
Example 3:  
In this example, COMPANY, PRODNO, and ORDERQTY are fetched from the COMPANY table and  
DAILY_ORDER table for the data in which the values of COMPNO and CUSTOMER are equal. All  
rows in the DAILY_ORDER table that have different values for COMPNO and CUSTOMER are also  
61  
fetched.  
[Figure: Example in which multiple tables are joined using a join table]  
62  
Example 3 illustrates coding RIGHT for the outer join type. If LEFT is coded for the outer join type in the same  
example statements, the results are as follows:  
5.3 Manipulating Data Using Aliases in Tables  
If column names are qualified by long table names, coding columns may become time-consuming. Instead, data can  
be manipulated by specifying correlation names for tables in table expressions. The following is an example of  
specifying correlation names:  
Example:  
In this example, correlation names are used as column name qualifiers in the query specification in  
Figure: Example in which multiple tables are joined and data manipulated. The result of the query  
specification is the same as in Figure: Example in which multiple tables are joined and data  
63  
manipulated.  
In this example, the correlation names "T1" and "T2" are specified for the ORDER table and the STOCK table,  
respectively. One of these correlation names is used as the column name qualifier. The correlation name is valid only  
in the specified SQL statement. If a correlation name is specified, the correlation name must be used in the column  
name qualifier. The original table name cannot be used as the column name qualifier.  
5.4 Joining a Table to Itself and Manipulating Data  
This manual has already explained comparing a value in one column with a value in another column and performing  
operations on values in rows of columns in tables. Comparing and manipulating values in different rows of the same  
table has not so far been possible. This section explains how to manipulate data in different rows by joining a table to  
itself as if joining it to a different table.  
To join a table to itself, specify the table name more than once in the FROM clause. Then, specify a correlation name  
in the SQL statement for each table so that the tables can be distinguished.  
Comparing different rows  
The following is an example of comparing values from different rows of the same table:  
Example 1:  
In this example, pairs of ITMNO for which PRODUCT is identical are obtained from the STOCK table.  
The columns to be fetched are the ITMNO pair and PRODUCT. To perform this data manipulation, the  
STOCK table is specified twice in the FROM clause and a table is derived that includes two identical  
columns. To distinguish identical columns, add the correlation name T1 to one column and T2 to the  
other.  
64  
[Figure: Example of joining a table to itself and manipulating data]  
65  
Operations on different rows  
The following is an example of performing an operation on values in different rows of the same table:  
Example 2:  
In this example, the difference in STOCKQTY for products for which ITMNO is consecutive in the  
STOCK table is obtained. The data to be fetched is ITMNO for both products, PRODUCT for the one  
with the higher ITMNO, and the difference in STOCKQTY.  
SELECT T2. ITMNO, T1. ITMNO, T2.PRODUCT, T2.STOCKQTY - T1.STOCKQTY AS STOCKQTYDIFER  
FROM STOCKS. STOCK AS T1, STOCKS. STOCK AS T2  
WHERE T2. ITMNO = T1. ITMNO + 1  
66  
[Figure: Example of performing an operation on different rows]  
5.5 Obtaining the Aggregate for Rows from Multiple Table  
s
The aggregate for rows contained in multiple tables can be obtained by joining two or more query specifications with  
"UNION". Specify the tables to be processed and the conditions for fetching data in the query specifications. In  
obtaining the aggregate, rows in which the column values are the same can be specified as one row. Also, the original  
row count can be specified without modification.  
Specifying duplicate rows as one row during aggregation  
The result of using UNION to join query specifications is that the rows for each query specification result are collected  
and a table is derived. In addition, duplicate rows are specified as one row. Duplicate rows are those rows in which the  
column values are the same. Figure: Example of specifying UNION in query expression shows the kind of tables that  
are derived from the result of using UNION to join query specifications.  
Example 1  
In this example, the aggregate is obtained for rows in columns COLA and COLC in table TBL1 and  
columns COLZ and COLX in table TBL2 in Figure: Example of specifying UNION in query expression.  
Tables TBL1 and TBL2 are contained in schema SCM.  
67  
[Figure: Example of specifying UNION in query expression]  
If UNION is used to join query specifications, a new table is derived corresponding to the columns in both query  
specification results. The column sequence is from the left. Each query specification must be specified as follows:  
· The number of columns in the results table must be the same as in the query specification.  
· The data type for columns for each corresponding results table must be comparable.  
For information about comparable data types, see Table "Comparable data types".  
The column names in the table for the query expression results are the column names specified in the query  
specification on the left. However, if the select column list in the query specification on the left is an operational  
expression or set function, the columns in the results table do not have names.  
Including duplicate rows in aggregate without modification  
Specify ALL and UNION to derive a table from all rows from each query specification result without having duplicate  
rows specified as one row. Figure: Example of specifying UNION ALL in query specification shows the kind of table  
derived when ALL and UNION are specified.  
Example 2:  
In this example, the aggregate is obtained for all rows in columns COLA and COLC in table TBL1 and  
columns COLZ and COLX in table TBL2 in Figure: Example of specifying UNION ALL in query  
specification. Duplicate rows are also included.  
68  
[Figure: Example of specifying UNION ALL in query specification]  
Aggregate sequence due to UNION  
Query specifications joined by UNION are processed in sequence from the left. However, parentheses can be used to  
specify a different evaluation sequence. A query expression enclosed in parentheses is processed before other query  
expressions. The following is an example of using parentheses in a query specification joined by UNION:  
Example 3:  
In this example, an aggregate is obtained for tables TBL1, TBL2, TBL3, and TBL4. First, duplicate rows  
in tables TBL1 and TBL2 are removed and the aggregate is obtained [1]. Next, duplicate rows in tables  
TBL3 and TBL4 are removed and the aggregate is obtained [2]. Finally, all rows, including the  
duplicated rows, in these results are included and the aggregate is obtained [3].  
69  
70  
Chapter 6 Methods of Manipulating Data  
This chapter describes methods of using SQL statements to manipulate data.  
This chapter contains the following sections:  
6.1 Grouping Tables and Manipulating Data  
6.2 Specifying Various Search Conditions  
6.3 Manipulating Numeric Data  
6.4 Manipulating Character String Data  
6.5 Manipulating Date Data  
6.6 Converting the Data Type to Manipulate Data  
6.7 Using CASE Expression to Manipulate Data  
6.8 Specifying Row IDs to Manipulate Data  
6.9 Manipulating Data using Parallel Query  
6.10 Omitting Schema Names  
6.11 Changing the User of the Current Session  
6.12 Adding a Name to a Results Column in Query Specification  
6.13 Manipulating Data Using Sequence  
6.1 Grouping Tables and Manipulating Data  
When data is fetched from tables, the table can be grouped according to column values and data can be manipulated.  
Grouping tables refers to a way of grouping rows in which a column or a group of columns have the same value. A  
different group is formed from different rows. A table that has been grouped in this way is called a grouped table.  
Specify the GROUP BY clause or the HAVING clause to group a table and manipulate data. The GROUP BY clause  
and the HAVING clause can be specified in a table expression. If the FROM clause, WHERE clause, GROUP BY  
clause, and HAVING clause are specified in the same table expression, the processing sequence is as follows:  
1. FROM clause  
2. WHERE clause  
3. GROUP BY clause  
4. HAVING clause  
First, the rows in the table specified by the FROM clause are specified by the search conditions in the WHERE clause.  
Next, the specified rows are grouped according to the GROUP BY clause. Then, the group is specified by the search  
conditions in the HAVING clause.  
6.1.1 Grouping tables and fetching data  
Grouping tables  
The first method of grouping tables is as follows:  
Example 1:  
In this example, the STOCK table is grouped according to the values for PRODUCT.  
"Figure: Example of a table derived from GROUP BY clause" shows the table derived from the GROUP BY clause.  
71  
[Figure: Example of a table derived from GROUP BY clause]  
A collection of rows derived from the FROM clause and WHERE clause in the table expression are grouped together.  
In this example, the result of the FROM clause is equivalent to the STOCK table because only the STOCK table is  
specified in the FROM clause in the table expression. Also, because the WHERE clause is omitted, all rows are  
grouped.  
GROUP BY clause  
Specify a column name as a group key, a data column value function, or a numeric function in the GROUP BY clause.  
When a column name is specified, the column is called a grouped column. When a data column value function or  
numeric function is specified, the function is called a grouped function. In Example 1, the grouped column is  
PRODUCT. The table derived from the results of the GROUP BY clause is a grouped table in which rows having the  
same value for the grouped column are formed into one group. When specifying multiple columns as grouped  
columns, separate the column names with commas (,). In this case, the rows having the same values for each  
grouped column are formed into one group.  
72  
Fetching data from a grouped table  
A query specification is used to derive a table with which data is to be manipulated. If a query specification is used to  
derive a table from a grouped table, the table is made up of a row for each group. In a query specification for which a  
GROUP BY clause is specified, a grouped column, a grouped function, or a set function can be specified in the select  
column list. If a set function is specified, the total, average, maximum, and minimum values, and the row count for the  
column can be obtained for each group. If a column other than a grouped column is specified by other than a set  
function, an error occurs. For example, suppose STOCKQTY is specified in the select column list in the query  
specification in Example 1. In this case, whether the value for STOCKQTY for PRODUCT TELEVISION is "85", "90",  
or "0" cannot be determined. However, a total value and an average value for these values can be determined, so a  
set function can be specified. Also, in Figure: Example of a table derived from GROUP BY clause, the values of  
WHCODE cannot be directly specified in the select column list despite the values being unique in each group. This is  
the case because a value that is not unique may be produced at any time due to variations in the data. If columns  
PRODUCT and WHCODE are specified as grouped columns for the data in Figure: Example of a table derived from  
GROUP BY clause, the values of WHCODE values can be fetched. A literal or host variable can be specified in the  
select column list  
Example 2:  
In this example, the totals for PRODUCT and STOCKQTY are obtained from the grouped table in  
Example 1.  
73  
[Figure: Example of fetching data by grouping a table]  
The reader probably already noticed in Figure: Example of a table derived from GROUP BY clause that the result of  
specifying only a grouped column in the select column list is the same as specifying "DISTINCT" in the query  
specification. In Example 2, only PRODUCT was specified in the select column list, so PRODUCT was obtained with  
duplicate rows deleted. However, if the objective is to delete duplicate rows, specify DISTINCT rather than specifying  
the GROUP BY clause to improve the processing efficiency of the SQL statements.  
To fetch the result of a set function and a column name as in Example 2, a table must be grouped. If the set function  
and column name are specified in the select column list but the GROUP BY clause is not specified, an error occurs.  
The following is an example in which an error occurs:  
Example 3:  
In this example, a set function is incorrectly used.  
SELECT PRODUCT, SUM (STOCKQTY) FROM STOCKS. STOCK  
74  
[Figure: Example of using a set function incorrectly]  
In Example 3, the row count for the result is one row for the total of STOCKQTY, but n rows for PRODUCT. Therefore,  
this query specification results in an error.  
Grouping tables by using parts of character strings  
Methods of grouping table data in columns have been explained so far. The following explains a method of grouping  
tables by using parts of character strings. Specify a character substring function in the GROUP BY clause. Figure:  
Example of grouping tables by using parts of character strings shows an example of the specification. For details on  
the character substring function, see 6.3 "Manipulating Numeric Data".  
Example 4:  
In this example, the city names of column "LOCATION" in the CUSTOMER table are grouped with the  
same persons in order to fetch the average age of each location.  
75  
[Figure: Example of grouping tables by using parts of character strings]  
Case-splitting data and grouping tables  
Specify a CASE expression in the GROUP BY clause to split column data into cases instead of columns, and to group  
tables for each value split into cases. An example of specification is shown below. For details on the CASE expression,  
refer to "6.7 Using Case Expression to Manipulate Data."  
Example 5  
The retail-sale history table is grouped for each generation, and the total proceeds are fetched for each  
generation.  
76  
[Figure: Case-splitting data and grouping tables]  
Grouping tables by month  
To group tables of a DATE-type column by year or month, specify the date-time value function. A specification  
example is shown below. For details on the date-time value function, see "6.5 Manipulating Date Data."  
Example 6  
The sales dates in the RETAILSALESHIST table are grouped by month, and the total proceeds are  
fetched.  
77  
[Figure: Example of grouping tables by month]  
6.1.2 Specifying groups to be processed from a grouped table  
The WHERE clause can be used in a table expression to specify rows to be processed in the table specified in the  
FROM clause. To do this, specifying the groups to be processed in a table grouped by the GROUP BY clause, using a  
HAVING clause. The following is an example of specifying the HAVING clause:  
Example:  
In this example, the totals of ITMNO and STOCKQTY are obtained for products with two or more  
occurrences of the same ITMNO from the grouped table in Figure: Example of a table derived from  
GROUP BY clause shows the table derived from the GROUP BY clause.  
78  
Figure: Example of a table derived from a HAVING clause shows the table derived from the HAVING clause and the  
query specification result.  
79  
[Figure: Example of a table derived from a HAVING clause]  
HAVING clause  
Specify the search conditions for the groups to be processed in the HAVING clause. The columns used as conditions  
must either be grouped columns or must be able to be specified by a set function. If a column other than a grouped  
column is specified by other than a set function, an error occurs.  
The WHERE clause can also specify search conditions for grouped columns. Whereas the HAVING clause targets  
grouped tables, the WHERE clause specifies the rows to be processed in the step before being grouped. Therefore,  
80  
specifying search conditions in the WHERE clause improves the processing efficiency of SQL statements. For  
example, to derive rows in which PRODUCT is TELEVISION, specify PRODUCT = 'TELEVISION' as the condition in  
the WHERE clause of the query specification in the previous example.  
6.1.3 Grouping multiple tables that are joined and manipulating data  
Specifying multiple tables in the FROM clause to join tables and manipulate data has already been explained. A table  
can be derived from the result of the FROM clause in the table expression. Another table can be derived from the  
result of a WHERE clause that specifies rows to be processed from the previously derived table. The GROUP BY  
clause targets this table for processing. The results of joining multiple tables can be grouped and data manipulated.  
The following is an example of specifying these clauses:  
Example 1:  
In this example, ITMNO and STOCKQTY for products for which the total of ORDER QTY is less than  
210 and for which orders exist for more than one CUSTOMER are added. Then, the total values for  
ITMNO and STOCKQTY from the STOCK table and the ORDER table are obtained.  
[Figure: Example of grouping multiple tables that are joined and manipulating data]  
81  
82  
A common error with this method of manipulating data is that the user may mistakenly believe that obtaining the totals  
of STOCKQTY and ORDERQTY for each ITMNO will provide the same result as in Example 1. The following is an  
example of using this method incorrectly:  
Example 2:  
In this example, the data manipulation used in Example 1 is incorrectly specified.  
Figure: Example of a common error when grouping multiple joined tables shows the result of the execution of this  
query specification.  
83  
[Figure: Example of a common error when grouping multiple joined tables]  
The MAX function or MIN function can be used to correct the previous error, as in the following example:  
Example 3:  
In this example, the error in Example 2 is corrected.  
Because the value of STOCKQTY is the same in each group, both the maximum and minimum values for each group  
can only be one value, the STOCKQTY value. Therefore, the result of this query specification is the same as in  
Figure: Example of grouping multiple tables that are joined and manipulating data. However, to improve the  
processing efficiency, the SQL statement should be specified as in Example 1.  
6.1.4 Recalculating calculation results for a grouped table  
To recalculate results that have already been calculated for a grouped table, use a derived table. When a derived table  
is used, the results can be recalculated without a view table definition. The following is a specification example:  
Example:  
Use the derived table used to get the total stock quantity for each product from the stock table, and get  
the sums for the number of products and stock quantity.  
84  
6.2 Specifying Various Search Conditions  
Search conditions can be specified in SQL statements to specify the manipulation of rows that satisfy the search  
condition. This section explains how to specify search conditions that use a single predicate and predicates combined  
by logical operators ("AND", "OR", or "NOT"). The result of a predicate or combined predicate is either "true", "false",  
or "undefined". If the result is "true", the search condition is satisfied. If the result is "false" or "undefined", the search  
condition is not satisfied.  
Specifying logical operators  
This section explains the functions of logical operators and how to specify them.  
Example 1:  
In this example, the logical operator "AND" is specified in the search condition. The rows from the  
STOCK table for which ITMNO is 100 or greater and WHCODE is 1 are fetched.  
Example 2:  
In this example, the logical operator "OR" is specified in the search condition. The rows from the  
85  
STOCK table for which ITMNO is 100 or greater or WHCODE is 1 are fetched.  
Example 3:  
In this example, the logical operator "NOT" is specified in the search condition. The rows from the  
STOCK table for all items except TELEVISION are fetched.  
Table: Truth table for AND lists the result of specifying AND between predicates. Table: Truth table for OR lists the  
result of specifying OR between predicates. Table: Truth table for NOT lists the result of specifying NOT before the  
predicate.  
[Table: Truth table for AND]  
[Table: Truth table for OR]  
86  
[Table: Truth table for NOT]  
Evaluation sequence of logical operators  
Multiple logical operators can be specified in a search condition. In this case, logical operators are evaluated in the  
following sequence:  
1. NOT  
2. AND  
3. OR  
However, parentheses can be used to in the specification of the evaluation sequence. A condition enclosed in  
parentheses is evaluated before other conditions.  
6.2.1 Comparing two values  
Using a comparison operator to compare two values  
Use a comparison predicate to compare two values. Table: Comparison operators lists the comparison operators that  
can be specified in a comparison predicate.  
87  
[Table: Comparison operators]  
The data types of the left and right value expressions of a comparison operator must be comparable. Table:  
Comparable data types lists comparable data types. For more information on data types, see Appendix A "SQL Data  
Types and Equivalent Host Variable Data Types".  
88  
[Table: Comparable data types]  
The following is an example of specifying a comparison predicate:  
Example 1:  
89  
In this example, rows for which ITMNO is 123 are fetched from the STOCK table.  
Example 2:  
In this example, rows for which STOCKQTY is less than 50 are fetched from the STOCK table.  
SELECT ... FROM STOCKS. STOCK WHERE STOCKQTY < 50  
Example 3:  
In this example, rows for which "PRICE x ORDERQTY" is 1,000,000 or less are fetched from the  
ORDER table.  
SELECT ... FROM STOCKS. ORDER  
WHERE PRICE * ORDERQTY <= 1000000  
Example 4:  
In this example, rows for which STOCKQTY is greater than 500 are fetched from the STOCK table.  
SELECT ... FROM STOCKS. STOCK WHERE STOCKQTY > 500  
Example 5:  
In this example, rows for which STOCKQTY is 500 or greater are fetched from the STOCK table.  
SELECT ... FROM STOCKS. STOCK WHERE STOCKQTY >= 500  
Example 6:  
In this example, rows for which PRODUCT is other than TELEVISION are fetched from the STOCK  
table.  
SELECT ... FROM STOCKS. STOCK WHERE PRODUCT < > 'TELEVISION'  
The following are examples in which logical operators are used to group and specify several comparison predicates.  
Example 7:  
In this example, rows for which PRODUCT is TELEVISION and STOCKQTY is 90 or greater are  
fetched from the STOCK table.  
SELECT ... FROM STOCKS. STOCK  
90  
WHERE PRODUCT = 'TELEVISION' AND STOCKQTY >= 90  
Example 8:  
In this example, rows for which STOCKQTY is 10 or greater and PRODUCT is VIDEO CASSETTE  
PLAYER, and rows for which STOCKQTY is 200 or greater and PRODUCT is TELEVISION are  
fetched from the STOCK table.  
SELECT ... FROM STOCKS. STOCK  
WHERE STOCKQTY >= 10 AND PRODUCT = 'VIDEO CASSETTE PLAYER'  
OR STOCKQTY >= 200 AND PRODUCT = 'TELEVISION'  
Example 9:  
In this example, rows for which PRODUCT is other than TELEVISION or VIDEO CASSETTE PLAYER  
are fetched from the STOCK table.  
SELECT ... FROM STOCKS. STOCK  
WHERE NOT PRODUCT = 'TELEVISION'  
AND NOT PRODUCT = 'VIDEO CASSETTE PLAYER'  
Using a subquery in a comparison predicate  
If a value specified in a comparison predicate in a search condition is a value derived from a function, a subquery can  
be coded in the search condition.  
The part of the subquery enclosed by parentheses has the same format as the query specification. However, for a  
subquery, the derived table must have one column. This means that one value expression (column name, function,  
and so on) must be specified in the left-hand side of the search condition. The following is an example of a  
comparison predicate in which a subquery is specified:  
Example 10:  
In this example, the average for PRICE is calculated from the ORDER table, and CUSTOMER is  
fetched from the ORDER table where PRICE is higher than average.  
91  
[Figure: Example of specifying a subquery in a comparison predicate to retrieve data]  
Example 11:  
In this example, the maximum value for ITMNO is fetched from the STOCK table, and the PRODNO  
rows for that value are deleted from the ORDER table.  
DELETE FROM STOCKS.ORDER  
WHERE PRODNO = (SELECT MAX (ITMNO) FROM STOCKS. STOCK)  
92  
[Figure: Example of specifying a subquery in a comparison predicate to delete data]  
Checking whether a value is a null value  
Use the NULL predicate to check whether a column value is a null value. The following are examples of specifying the  
NULL predicate:  
Example 12:  
In this example, rows for which WHCODE is a null value are fetched from the STOCK table.  
Example 13:  
In this example, rows for which WHCODE is not a null value are fetched from the STOCK table.  
93  
6.2.2 Checking whether a value is in a certain range  
Use the BETWEEN predicate to check whether a value is in a certain range. The following are examples of specifying  
the BETWEEN predicate:  
Example 1:  
In this example, rows for which STOCKQTY is greater than or equal to 50 but less than or equal to 100  
are fetched from the STOCK table.  
The result of the BETWEEN predicate is true if the following conditions, based on the three value expressions, are  
satisfied:  
The result is false if value expression [1] is less than value expression [2] or greater than value expression [3].  
The result is undefined if value expression [1] is a null value or is greater than or equal to value expression [2], and  
value expression [3] is a null value. The result is also undefined if value expression [1] is less than or equal to value  
expression [3] and value expression [2] is a null value.  
Example 2:  
In this example, rows for which STOCKQTY is greater than or equal to 10 but less than or equal to 50,  
or greater than or equal to 200 but less than or equal to 300 are fetched.  
SELECT ... FROM STOCKS. STOCK  
WHERE STOCKQTY BETWEEN 10 AND 50  
OR STOCKQTY BETWEEN 200 AND 300  
Example 3:  
In this example, rows for which PRICE x ORDERQTY is less than 1,000,000 or greater than 2,000,000  
are fetched from the ORDER table.  
94  
A comparison predicate can also be used to check whether a value is in a certain range. In the following example, the  
specified comparison predicate has the same effect as the BETWEEN predicate in Example 1:  
Example 4:  
In this example, rows for which STOCKQTY is greater than or equal to 50 but less than or equal to 100  
are fetched from the STOCK table.  
6.2.3 Checking whether values are contained in a set of values  
Checking whether values are contained in a set of values  
Use the IN predicate to check whether certain values are contained in a set of quantified values. Use a quantified  
value list or a subquery to specify the set of values to be compared. The following is an example of specifying a  
quantified value list:  
Example 1:  
In this example, any row in the STOCK table for which ITMNO is 123, 124, 226, or 227 is specified.  
Figure: Example of specifying IN predicate shows the result of specifying this quantified value list.  
95  
[Figure: Example of specifying IN predicate]  
The values to be compared in the value expression are enclosed in parentheses and specified in a quantified value list.  
Use host variables or literals to specify values in a quantified value list. Column names cannot be specified.  
The result of the IN predicate is true if the value for the value expression is equal to one of the values enclosed in  
parentheses. The result is false if the value for the value expression does not equal one of the values enclosed in  
parentheses. The result is undefined if the value of the value expression is a null value or if all the values enclosed in  
parentheses are null values.  
Using a subquery in the IN predicate  
If the values to be compared to the value expression are values derived from another table, enter a subquery instead  
of a quantified value list.  
The format of the subquery specification in parentheses is the same as that for a query specification. However, the  
table derived from the subquery must have only one column. This means that one value expression (such as a column  
name or a function) can be specified in the left-hand side of the search condition. The following is an example of the  
IN predicate in which a subquery is used:  
Example 2:  
In this example, the names of companies that have ordered the product that has ITMNO (PRODNO)  
"351" are fetched.  
96  
[Figure: Example of using a subquery in the IN predicate]  
Comparing a set of values  
Use a quantified predicate as a search condition to specify comparison with a set of quantified values. Use a  
comparison operator and quantifier (ALL, SOME, or ANY) in specifying the quantified predicate.  
The format of the subquery specification in parentheses is the same as that for a query specification. However, a table  
derived from a subquery can only have one column. This means that only one value expression (column name,  
function, and so on) can be specified on the left-hand side of the search condition.  
A column that cannot be included in a table specified by a subquery (but can be included in a table specified by an  
outer subquery) can be specified in the search condition of the subquery. This subquery is called a subquery that has  
an outer reference. The following is an example of a subquery that has an outer reference specified in the quantified  
predicate:  
Example 3:  
In this example, values of ITMNO for which ORDERQTY for all CUSTOMER exceeds STOCKQTY are  
fetched. The column that cannot be included in the subquery table (ORDER table), that is, the column  
(ITMNO) that can be included in the outer query table (ORDER table), is specified in the search  
condition. Figure: Example of specifying a quantified predicate shows the retrieval results.  
97  
[Figure: Example of specifying a quantified predicate]  
98  
Checking whether rows that satisfy certain conditions exist  
The EXISTS predicate can be specified in a subquery. Whether rows are specified depends on whether or not the  
result of the subquery is null. In other words, the EXISTS predicate is used when the row count for the table derived  
from the result of the subquery can be 0. If the result of the subquery specified after EXISTS is not null, the EXISTS  
predicate is true. If the result of the subquery is null, the EXISTS predicate is false.  
The format of the subquery specification in parentheses is the same as that for a query specification. The result of the  
subquery can be a table with two or more columns. The following is an example of specifying the EXISTS predicate:  
Example 4:  
In this example, data for ITMNO and PRODUCT for products for which the value of orders is greater  
than 5,000,000 is fetched. The value of orders is obtained by PRICE x ORDERQTY.  
99  
[Figure: Example of specifying the EXISTS predicate]  
6.2.4 Checking whether values match a character-string pattern  
Use the LIKE predicate to check whether data in a character-string type or national-language character-string type  
matches a certain character-string pattern. The result of the LIKE predicate is true if the specified pattern is contained  
in a column. The result is false if the pattern is not contained in any column. The result is undefined if the specified  
columns or pattern have a null value. Data in character-string or national-language character-string type, and blank  
characters in a pattern are treated as a single alphabetic or national-language character.  
If the string or pattern specified in the LIKE predicate is a variable-length character string, data within the valid range is  
evaluated. The following is an example of specifying the LIKE predicate:  
Example 1:  
In this example, rows where "JAPAN" appears in the ADDRESS column of the COMPANY table are  
specified. Figure: Using the LIKE predicate to specify rows shows the result of this specification.  
100  
[Figure: Using the LIKE predicate to specify rows]  
Use a character string, arbitrary string specifier, or arbitrary character specifier to represent the pattern. Table:  
Arbitrary string specifier and arbitrary character specifier explains the arbitrary string specifier and the arbitrary  
character specifier. When the pattern is a character string, use alphabetic characters to specify the pattern. When the  
pattern is in a national language, use national-language characters to specify the pattern.  
[Table: Arbitrary string specifier and arbitrary character specifier]  
Examples of using arbitrary string specifier %  
Examples of using arbitrary string specifier% are shown below. "Figure: Example of LIKE predicate specification  
(using an arbitrary string specifier)" shows which row is specified. Schema SCM contains the table TBL5 used in these  
examples. SCM.  
Example 1:  
101  
Rows on which the value in column COL1 in table TBL5 is "ABC" are specified.  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'ABC'  
Example 2:  
In this example, rows for which the value in column COL1 in table TBL5 begins with "ABC" are  
specified.  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'ABC%'  
Example 3:  
Rows on which the value in column COL1 in table TBL5 begins with "ABC" are specified.  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE '%ABC'  
Example 4:  
Rows containing "ABC" at any position in column COL1 of table TBL5 are specified.  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE '%ABC%'  
Example :5  
Rows on which the value in column COL1 in table TBL5 begins with "AB" and ends with "C" are  
specified.  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'AB%C'  
Example :6  
Rows on which the value in column COL1 in table TBL5 begins with "ABC" and ends with a blank are  
specified.  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'ABC% '  
Example 7:  
Rows on which the value in column COL1 in table TBL5 begins with "ABC" are specified. To define the  
pattern to host variable HOST1 (fixed size of 6 bytes), specify "ABC%". Use the TRIM function to  
remove blanks at the end.  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE TRIM(TRAILING :HOST1)  
Example 8:  
Rows on which the value in column COL1 in table TBL5 begins with "ABC". To define the pattern to  
variable-length host variable HOST1, specify "ABC%".  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE :HOST1  
102  
[Figure: Example of LIKE predicate specification (using an arbitrary string specifier)]  
Examples of using arbitrary string specifier "_"  
Examples of using arbitrary string specifier "_" are shown below. The figure with the example of "Figure: LIKE  
predicate specification (using an arbitrary string specifier)" shows which row is specified.  
Example 1:  
Rows of four characters for which the value in column COL1 in table TBL5 begins with "ABC" and ends  
with any character are specified.  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'ABC_'  
Example 2:  
Row of six characters for which the value in column COL1 in table TBL5 begins with "ABC" and ends  
with any three characters are specified.  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'ABC___'  
Example 3:  
Rows of four characters for which the value in column COL1 in table TBL5 begins with any character  
and ends with "ABC" are specified.  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE '_ABC'  
Example 4:  
Rows of five characters for which the value in column COL1 in TBL5 begins with any two characters  
and ends with "ABC" are specified.  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE '__ABC'  
Example 5:  
103  
Rows of four characters for which the value in column COL1 in TBL5 begins with any character  
followed by "A" and ends with "BC".  
SELECT ... FROM SCM.TBL5 WHERE COL1 LIKE 'A_BC'  
[Figure: Example of LIKE predicate specification (using an arbitrary string specifier) ]  
Escape characters  
In a pattern specification in the LIKE predicate, the percent and underscore signs specify the location of the arbitrary  
characters. If the percent and underscore signs themselves are to be searched, specify an escape character.  
Examples of specifying the escape character follow. Figure: Example of specifying a LIKE predicate specification  
using escape characters shows the characters specified in the examples.  
Example 6:  
In this example, rows for which the value in column COL2 in table TBL5 is "ABC%" are specified.  
The character following the word "ESCAPE" is the escape character. In a character pattern, the escape character is  
specified immediately before the percent or underscore sign to be searched. Any character can be specified as the  
escape character.  
Example 7:  
In this example, rows for which the value in column COL2 in table TBL5 table is "ABC%%" are  
specified.  
104  
Example 8:  
In this example, rows for which the value in column COL2 in table TBL5 begins with any two characters  
and ends with "_ _ABC_ _" are specified.  
[Figure: Example of specifying a LIKE predicate specification using escape characters]  
6.3 Manipulating Numeric Data  
Users can manipulate numeric data to obtain absolute values, minimum integer values, maximum integer values, and  
other values. The table below lists the processing used to manipulate numeric data.  
[Table: Processing for manipulating numeric data]  
Example 1:  
105  
For products with a high variation of shipment quantity (assuming that the difference between the  
preceding and current shipment quantities is 10 or greater), the corresponding product numbers are  
obtained from the SHIPTBL table.  
Example 2:  
The maximum and minimum temperatures in each region are rounded down to the next integers.  
106  
Example 3:  
The maximum temperature in each region is rounded off to two decimal places.  
6.4 Manipulating Character String Data  
When manipuling table data, users can manipulate and join character strings. The table below lists the processing for  
manipulating parts of character strings.  
107  
[Table: Processing for manipulating parts of character strings]  
Example 1:  
Company names and addresses are fetched from the company table. For each company name, a null  
character at the end is removed, "Co., Ltd.," is concatenated, and then only the metropolis and district  
names are fetched from the address.  
Users can fetch a part of a specific character string and join a value to a character string in a specific  
column. The TRIM function removes characters from the beginning of a value expression if LEADING  
is specified in the TRIM function; it removes them from the end of a value expression if TRAILING is  
specified; and it removes them from both ends of a value expression if BOTH is specified.  
To concatenate character strings, join them to concatenate operator "||". To do this, the data type of the  
column must be the character type. The data types of both terms to be concatenated must be the  
character type, and they must be comparable. For details on the comparable data types, see Table:  
Comparable Data Types.  
In the character substring function, SUBSTRING is specified to obtain a substring of a specific  
character string length beginning from the starting position in a value expression.  
108  
[Figure: Example of combining character value functions to fetch data]  
Example 2:  
Codes and office names are fetched from the office list table, and character string "OFFICE" is  
removed from each office name.  
109  
[Figure: Example of combining a character substring function with a numeric function to fetc  
h data]  
Example 3:  
The last three characters are fetched from the product name character string in 2NDTBL.  
[Figure: Example of combining a character substring function with a numeric function to upd  
ate data]  
Example 4:  
An asterisk "*" is embedded on the left end of each branch name. The entire length is five characters.  
110  
Example 5:  
Character string "VIDEO" in COL2 of table 2NDTBL is replaced with "VIDEOTAPE".  
6.5 Manipulating Date Data  
Users can manipulate table data to calculate dates and times, convert date-time value expression data to characters,  
and convert character-type data to DATE-type data. The table below lists the processing for manipulating date data.  
111  
[Table: Processing for manipulating date data]  
Example 1:  
The order quantity for each quarter is obtained from the order table.  
By using the TRUNC_DATE function, the date can be truncated for each year (January 1 on the year),  
month (1st day of the month), or quarter.  
112  
Example 2:  
The number of days that have been elapsed from the order date to the current date is obtained from  
the ORDERTBL table. The current date is 2000-08-10.  
By using the SPAN_DATE function, the difference (time interval) between dates can be obtained,  
where YEAR, MONTH, or DAY is used as the unit for the time interval. Users can also obtain the  
number of times the year, month, or day changes between dates.  
113  
Example 3:  
For orders that are one month old or older in the ORDERTBL table, the corresponding order numbers  
are obtained from the order table according to the specified order day. The current date is 2000-08-01.  
By using the ADD_DATE function, dates can be added, where YEAR, MONTH, or DAY is used as the  
unit of addition for the time interval. The added number can be defined as a number of years, months,  
or days.  
Example 4:  
A number of days ranging from the order day until the end of month is obtained.  
114  
The last date of the month can be obtained using the LAST_DAY function.  
Example 5:  
The maximum temperature date data in the WEATHERMNTBL table is converted to a character string  
in the "YYYY.MM.DD DY" format. The date type can be converted to the character type using the  
CNV_CHAR function. The conversion is performed in the date format.  
115  
Example 6:  
The minimum temperature date in the WEATHERMNTBL table is converted to character-type data in  
the date format. The date format "DAY MONTH DD, YYYY" is assumed to be specified in CHAR-type  
host variable :DAYFMT.  
Example 7:  
The date characters in MINTEMPDAYCHARTBL are converted to a date.  
The character-type data defined in the date format can be converted to date-time-type data using the  
CNV_DATE function.  
116  
6.6 Converting the Data Type to Manipulate Data  
The data types of columns can be converted to execute date or time operations using character strings and to add or  
update data in time type or time interval type columns.  
An example of a date operation executed by converting the character string data type to the time interval type (year  
and month) is given below.  
Example 1:  
In this example, codes and dates obtained by adding six months to the process date are fetched from  
the OFFICE table. Also, "6" has been specified for character string type host variable CDATE.  
117  
[Figure: Converting data from character string type to time interval type (year and month)]  
An example of a time operation executed by converting the character string data type to the time interval type (date  
and time) is given below.  
Example 2:  
In this example, the process time is updated to a value reduced by 1 hour and 30 minutes for data in  
the DAILY_ORDER table whose ORDERQTY is less than 10. Also, "1:30" has been specified for  
character string type host variable OTIME.  
UPDATE STOCKS.DAILY_ORDER  
SET PROCESS_TIME = PROCESS_TIME - CAST(:OTIME AS INTERVAL HOUR TO MINUTE)  
WHERE ORDERQTY < 10  
[Figure: Converting data from character string type to time interval type (date and time)]  
An example of converting the numeric value data type to the character string type and adding to a character string to it  
is given below.  
Example 3:  
In this example, PRODNO and PRICE are fetched from the ORDER table for data for CUSTOMER 61,  
and $ is prefixed to the price.  
SELECT PRODNO, '$' || CAST(PRICE AS CHARACTER(6))  
FROM STOCKS.ORDER WHERE CUSTOMER = 61  
118  
[Figure: Converting data from numeric type to character string type]  
6.7 Using CASE Expression to Manipulate Data  
The CASE expression is used to change the value of a results column depending on the column value, arrange  
fetched columns in a sequence based on priority level, and split data to cases to specify an appropriate value to each  
case.  
Examples of using the CASE expression are as follows.  
Example 1:  
The number of services used for each user is obtained from history table HISTORYTBL.  
Example 2:  
According to the number of points, the users and points indicated by a symbol (PLATINUM, GOLD, or  
SILVER) are obtained from point table POINTTBL.  
119  
Example 3:  
The first setting items are obtained from the user table in the sequence of e-mail address, phone, and  
address.  
Example 4:  
The name of users and points of the users who reach the next rank by 500 points are obtained from  
the POINTTBL table. The number of points of each rank are as follows: SILVER is 3,000 points; GOLD  
is 10,000 points; and PLATINUM is 50,000 points.  
120  
Example 5:  
The services used are grouped, and the total number of services used for each group is obtained, from  
history table HISTORYTBL.  
Example 6:  
The service symbols listed in history table HISTORYTBL are changed.  
121  
6.8 Specifying Row IDs to Manipulate Data  
A row ID (ROW_ID) recognizes a unique row of a table in the database. Use a single row SELECT statement or  
FETCH statement to fetch a row ID. The host variable for receiving the row ID must correspond to the ROW_ID. For  
the variable definition corresponding to each data type, see Table "SQL data types and equivalent C language host  
variable data types" and Table "SQL date types and equivalent data types for COBOL host variables". Specify the  
fetched row ID in the following search conditions to manipulate the row data.  
· Search condition of the UPDATE statement:search  
· Search condition of the DELETE statement:search  
· Search condition of the WHERE clause in a query specification  
The following shows an example of specifying row IDs.  
Example:  
In this example, data is updated by using row IDs.  
6.9 Manipulating Data using Parallel Query  
If a key item in the data search is known for jobs such as ordinary jobs, the efficiency of the data search can be  
improved by preparing an index or similar facility. If the database for an ordinary job is used for document output in  
batch processing at night, the search time may increase. To improve the efficiency of the batch processing, divide the  
database into DSIs and perform a parallel search of the data in each DSI. This processing is called parallel query. To  
use parallel query, make the following specifications:  
· Specify PARALLEL for parallel query in the cursor declaration.  
· Specify SQL96 for SQL_LEVEL in the application environment file.  
· Specify the maximum number of parallel instances in MAX_PARALLEL in the application environment file.  
For the division of a database into multiple DSIs, refer to the "RDB User's Guide: Database Definition." For application  
environment files, refer to the "RDB User's Guide: Application Program Development."  
The following is an example of specifying a parallel query.  
Example:  
Determine the sales amount for each product code by year and month.  
122  
123  
6.10 Omitting Schema Names  
In the examples of SQL statements provided so far, table names have been qualified with a schema name, such as in  
STOCKS.STOCK. This section explains how to use a table declaration in which the schema name qualifier is not  
specified.  
Specify the table name used by the application program and the name of the schema in which the table is located in  
the table declaration. After the table declaration has been specified, when an SQL statement is entered, the schema  
name qualifier can be omitted from the table name specification. The following is an example of specifying a table  
declaration:  
Example:  
In this example, the STOCK table, the ORDER table, and the COMPANY table have been declared in  
the table declaration so the schema name qualifier can be omitted from the table name specification.  
124  
6.11 Changing the User of the Current Session  
When an application program accesses a database, the user of the current session becomes the person connected to  
the database. This user can be changed in the application program.  
Example:  
In this example, the user of the current session is changed to USER2.  
6.12 Adding Name to Results Column in Query Specificati  
on  
If a function is specified in the select column list of the query specification, no name is added to the derived columns.  
To add a name, use the AS clause and add a column name. An example of specification is shown below.  
Example:  
The total stock quantity for each product name is fetched from stock table STOCKTBL. In this example,  
use the table derived from the query specification to add a name to the total stock quantity column.  
125  
6.13 Manipulating Data Using Sequence  
CURRVAL and NEXTVAL can be used to fetch the created sequence number. CURRVAL fetches the current  
sequence number in the application program. NEXTVAL fetches the next value after the last sequence number  
fetched in the RDB system.  
CURRVAL and NEXTVAL can be specified in the following parts of SQL statements in the application program or  
stored procedure:  
· Select column list of a single row SELECT statement  
· Select column list for a query specification (query expression for cursor specification only)  
· SET clause of the UPDATE statement  
· Select column list for a query specification specified in the INSERT statement  
· VALUES clause of the INSERT statement  
· SET statement of a stored procedure  
· Triggered SQL statement  
CURRVAL and NEXTVAL cannot be specified in the following parts of SQL statements:  
· Search condition of a WHERE clause  
· Select column list for a subquery  
· DEFAULT clause of ALTER TABLE ADD COLUMN  
For the SELECT statement, sequence numbers are assigned to the rows of a search result.  
For the UPDATE statement, sequence numbers are assigned to update rows. For the INSERT statement, sequence  
numbers are assigned to the insertion rows of the insertion destination. In either case, if there are several search  
results, sequence numbers are assigned to each row of the results.  
To fetch the first sequence in a session, use NEXTVAL.  
If several NEXTVALs of the same sequence are specified in one SQL statement, the same sequence number is  
returned for all the NEXTVALs. If both the NEXTVAL and CURRVAL of the same sequence are specified in one SQL  
statement, the same sequence number is returned for both without reference to the order in which they appear in the  
SQL statement.  
Example 1:  
126  
This example shows the method for specifying a sequence in the SQL statement of an application  
program. In the example, employee numbers (EMPLOYEENO) are incremented and assigned to new  
employees.  
Example 2:  
This example shows the method for automatically inserting a sequence number in an insertion line  
using a sequence. In the example, new order numbers (ORDERNO) are assigned by incrementing  
order numbers (ORDERNO) in the order table (ORDERTBL).  
To insert a sequence number automatically into an insertion line, use the DEFAULT clause to specify a  
sequence in a table definition column. In the INSERT statement of the application program, omit the  
column where a sequence is specified in the DEFAULT clause or specify the DEFAULT clause as the  
insertion value.  
127  
Example 3:  
This example shows the method for referencing the sequence numbers for stock control (STOCKCTL)  
and sequence 1 (SEQ-1) by specifying CURRVAL and NEXTVAL.  
128  
129  
130  
Chapter 7 Executing Dynamic SQL Statements  
When Application Programs are Executed  
This chapter describes the functions and uses of dynamic SQL.  
This chapter consists of the following sections:  
7.1 Overview of Dynamic SQL  
7.2 Dynamically Modifying and Executing SQL Statements  
7.3 Dynamically Modifying and Executing SQL Statement Conditions  
7.4 Immediately Executing SQL Statements  
7.5 Changing Database Names and Omitted Schema Names  
7.1 Overview of Dynamic SQL  
Dynamic SQL is used to generate and execute SQL statements when application programs are executed. For  
example, suppose an application program to delete rows from a table is to be developed using delete row search  
conditions entered from an external data set. In this case, SQL statements to delete rows cannot be specified since  
the search condition to delete rows cannot be determined when the program is coded. SQL statements cannot be  
completed until the search condition is read from the external data set when the application program is executed.  
Embedded SQL statements cannot be used to write this kind of application program since SQL statements must be  
completed at compilation because the embedded SQL statements are interpreted at compilation. Use dynamic SQL  
for this kind of application program. With dynamic SQL, the SQL statements are interpreted and executed at  
application program run-time.  
This section explains the following elements required to execute dynamic SQL:  
· Overview of Dynamic SQL  
· Preparable statements  
· Dynamic parameter specification  
· USING clause  
SQLDA structure and SQL descriptor area  
If SQL statements are dynamically modified to manipulate data (i.e., if an SQL statement is entered outside an  
application program), the SQL statements to be entered are unknown when an application program is created. When  
data is fetched from a database, for example, the variable used to fetch the data must be declared in the application  
program. However, if the SQL statement to be entered is not known, a variable cannot be declared. This situation is  
the same as when data is entered in the database. Therefore, when modifying SQL statements dynamically, allocate a  
temporary descriptor area for data manipulation. When this descriptor area is used, data can be manipulated simply  
by declaring a proper variable.  
The descriptor area is used as an SQLDA structure or an SQL descriptor area. The SQLDA structure can be used  
only in C.  
SQLDA structure  
By dynamically allocating and using an SQLDA structure area in an application program, the user can directly  
manipulate SQLDA structure information.  
When the SQLDA structure is used, processing is simplified only if the dynamic parameter specification and select  
column list information are set in an SQLDA structure member or are obtained from a member.  
The SQLDA structure is positioned as shown in the figure below.  
131  
SQL descriptor area  
An SQL statement may be dynamically modified to manipulate data. In other words, the SQL statement may be  
entered from outside the application program. In this case, however, the type of SQL statement to be entered cannot  
be determined when the application program is created. For example, a variable used to fetch data from a data base  
must normally be declared in the application program. However, if the type of SQL statements to be entered is not  
known, the variable cannot be declared. This is also the case when data is to be inserted into a data base.  
So, the system allocates a temporary work area, the SQL descriptor area, to manipulate data to dynamically modify  
SQL statements. After declaring appropriate variables in the application program, use this work area to manipulate  
data. The location of the SQL descriptor area is as follows.  
Preparable statements  
Certain SQL statements can be dynamically created and executed at run-time after preparation. These statements  
are:  
· Single row SELECT statement  
· INSERT statement  
· UPDATE statement (searched)  
· DELETE statement (searched)  
· Multiple row SELECT statement  
· UPDATE statement (positioned)  
· DELETE statement (positioned)  
· Schema definition statement  
· Schema manipulation statement  
· Storage structure definition statement  
· Storage structure manipulation statement  
· User control statement  
· Access control statement  
Of these SQL statements, the following can only be used in this way: single row SELECT statement, multiple row  
SELECT statement, UPDATE statement (positioned), and DELETE statement (positioned). The syntax of the single  
row SELECT statement is the same as that of the query specification.  
132  
The SET USER PASSWORD statement (user control statement) and SET ROLE statement (access control  
statement) cannot be executed as preparable statements.  
The syntax of the multiple row SELECT statement is the same as that of the cursor specification.  
To execute these statements, the statements must be prepared for execution. These statements then become  
prepared statements.  
The following are examples of preparable statements:  
Example 1:  
In this example, a single row SELECT statement is specified. This statement targets rows to be fetched  
in which ITMNO is "110" in a table containing columns STOCKQTY and WHCODE derived from the  
STOCK table.  
SELECT STOCKQTY, WHCODE FROM STOCKS. STOCK WHERE ITMNO = 110  
Example 2:  
In this example, a single row SELECT statement is specified. This statement fetches values in  
ascending order from column STOCKQTY in a table containing columns PRODUCT and STOCKQTY  
derived from the STOCK table.  
SELECT PRODUCT, STOCKQTY FROM STOCKS. STOCK ORDER BY STOCKQTY ASC  
Example 3:  
In this example, an UPDATE statement (positioned) is specified. This statement changes PRODUCT to  
"BATTERY" and WHCODE to "5" in the row in which cursor CU1 is positioned.  
UPDATE STOCKS. STOCK  
SET PRODUCT = 'BATTERY,' WHCODE = 5 WHERE CURRENT OF CU1  
Dynamic parameter specification  
To specify a value specification (input variable) in a preparable statement, enter a question mark "?" in the value  
specification section. The question mark is known as the dynamic parameter specification. The dynamic parameter  
specification is equivalent to the variable specification in an embedded SQL statement. Dynamic parameter  
specifications can be specified in the following locations in preparable statements:  
· In the search condition of a dynamic SELECT statement, single row SELECT statement, UPDATE statement  
(searched), or DELETE statement (searched)  
· In the set clause of an UPDATE statement (searched) or dynamic preferable UPDATE statement (searched)  
· In the insert value list of an INSERT statement  
The following are examples of dynamic parameter specifications:  
Example 4:  
In this example, the dynamic parameter specification is specified in the search condition of a DELETE  
statement (searched).  
DELETE FROM STOCKS. STOCK WHERE PRODUCT = ?  
Example 5:  
In this example, the dynamic parameter specification is specified in the set clause of an UPDATE  
statement (searched).  
UPDATE STOCKS. STOCK  
133  
SET STOCKQTY = 0, WHCODE = ? WHERE ITMNO = 111  
Example 6:  
In this example, the dynamic parameter specification is specified in the insert value list of an INSERT  
statement.  
INSERT INTO STOCKS. STOCK (ITMNO, PRODUCT, STOCKQTY) VALUES(?, ?, ?)  
USING clause  
Use the USING clause to set values in the dynamic parameter specification of a prepared statement and to fetch the  
execution results. Table: Uses of the USING clause and its specification format lists the uses of the USING clause and  
its specification format.  
[Table: Uses of the USING clause and its specification format]  
With reference to the EXECUTE statements in Table: Uses of the USING clause and its specification format, the  
USING clause used to set the value of the dynamic parameter specification is known as the parameter USING clause.  
The USING clause used to fetch the execution results is known as the result USING clause.  
There are two USING clause specification formats:  
· Argument specification  
· Specification of a variable name or the SQL descriptor name of an SQLDA structure variable  
The first is referred to as a USING variable and the second as a USING descriptor.  
The method for specifying the USING descriptor in the EXECUTE statement is shown below.  
7.2 Modifying and Executing SQL Statements Dynamically  
This section explains how to modify and execute SQL statements dynamically.  
· Preparing SQL statements and manipulating the SQLDA structure  
- Preparing and executing a dynamic SELECT statement (for SQLDA structure)  
- Preparing and executing a dynamic single row SELECT statement (for SQLDA structure)  
· Preparing SQL statements and manipulating the SQL descriptor area  
- Preparing and executing a dynamic SELECT statement (for the SQL descriptor area)  
- Preparing and executing a dynamic single row SELECT statement (for the SQL descriptor area)  
7.2.1 Preparing SQL statements and manipulating the SQLDA structure  
When data is fetched from a database using dynamic SQL, specify the target that will receive the execution results.  
For the specified target to be able to receive the execution results, the select column list information must match the  
attributes of the specified target. Matching is accomplished by fetching the select column list information from the  
database to the SQLDA structure, then modifying the attributes. The SQLDA structure is positioned as shown in the  
134  
figure below.  
The SQLDA structure provides information about the target specification and dynamic parameter specifications.  
The SQLDA structure consists of SQLVAR (item descriptor area), SQLN (maximum number of SQLVAR elements),  
and SQLD (number of valid elements).  
The format of the SQLDA structure is as follows.  
The configuration element members of the SQLDA structure have the following meaning:  
135  
SQLN  
SQLN indicates the maximum number of the dynamic parameter specifications that can be written or the maximum  
number of the select column list items that can be written. SQLN therefore determines the maximum number of  
elements of array SQLVAR.  
SQLD  
SQLD determines the number of valid elements of array SQLVAR.  
Executing a output DESCRIBE statement specifying an SQLDA structure when the prepared statement contains  
select column list items returns the number of select column list items to SQLD.  
Executing an input DESCRIBE statement specifying an SQLDA structure when dynamic parameter specifications are  
written in the prepared statement returns the number of dynamic parameter specifications to SQLD.  
SQLVAR  
SQLVAR is an array that stores various kinds of information for dynamic parameter specifications or select column list  
items.  
Information for this array must be set before an SQL statement is executed for a dynamic parameter specification. An  
SQL statement in which n dynamic parameter specifications are written is executed as a prepared statement based  
on the information stored in SQLVA[0] to SQLVAR[n-1].  
Information for this array must be set before an SQL statement is executed for a select column list. If the SQL  
statement in which n select column lists are written is executed as a prepared statement, data is stored in the data  
buffers specified in SQLVAR[0] to SQLVAR[n-1] after the SQL statement has been executed.  
Item descriptor area SQLVAR consists of the items listed in the following table.  
The table below shows the correspondence between the TYPE values (data-type codes) of the SQLDA structure and  
SQL data types.  
136  
If the TYPE value is 9, indicating the datetime type, any of the following DATE_TIME_INTERVAL_CODE values is set  
in SQLSCALE:  
If the TYPE value is 10, indicating the interval type, any of the following DATETIME_INTERVAL_CODE values is  
stored in SQLSCALE:  
137  
Procedure for fetching execution results  
The order in which execution results are fetched using the USING descriptor is shown in "Figure: Procedure for  
fetching execution results for a prepared statement." The flow of fetching execution results is explained here. Each  
SQL statement is explained after "Figure: Procedure for fetching execution results for a prepared statement."  
138  
[Figure: Procedure for fetching execution results for a prepared statement]  
When modifying an SQL statement dynamically to manipulate data, prepare execution of the SQL statement.  
Preparing the SQL statement  
Use the PREPARE statement to prepare a dynamic SQL statement for execution. To create and execute an SQL  
statement when the program is executed, store the SQL statement as a character string in a host variable called the  
SQL statement variable. Next, the PREPARE statement to prepare the SQL statement for execution. At this point, the  
SQL statement stored in the SQL statement variable is associated with the SQL statement identifier for statement  
identification.  
If the SQL statement variable is a preparable dynamic UPDATE statement (positioned) or a preparable dynamic  
DELETE statement (positioned), the corresponding cursor must be opened in advance. The specified cursor must  
also be a dynamic cursor.  
If the PREPARE statement is reexecuted for the defined SQL statement identifier, the allocated prepared statement is  
deallocated and a new prepared statement is prepared. If the allocated prepared statement is a dynamic SELECT  
statement, the statement prepared for a preparable dynamic UPDATE or DELETE statement (positioned) that  
139  
references the cursor corresponding to the SQL statement identifier is also deallocated. However, if the prepared  
statement is a dynamic SELECT statement, the cursor corresponding to the SQL statement identifier must have  
already been closed.  
An example of specifying the PREPARE statement is shown below. The SQL statement to be prepared for execution  
is assumed to be stored in SQL statement variable str. The SQL statement identifier of this SQL statement is defined  
as STM.  
Deallocating an SQL statement  
Use the DEALLOCATE PREPARE statement to deallocate the prepared statement prepared by the PREPARE  
statement.  
If the prepared statement is a dynamic SELECT statement, the statement prepared for a preparable dynamic  
UPDATE or DELETE statement (positioned) that references the cursor corresponding to the SQL statement identifier  
is also deallocated. In this case, the cursor must have already been closed.  
An example of specifying the DEALLOCATE PREPARE statement is shown below. The prepared statement for which  
the PREPARE statement defined the SQL statement identifier as STM is assumed to be the prepared statement that  
will be deallocated.  
SQL statements used to manipulate the SQLDA structure  
As shown in "Figure: Procedure for fetching execution results for a prepared statement," use the DESCRIBE  
statement to fetch select column list information from the database to the SQLDA structure. The DESCRIBE  
statement and its functions are explained below.  
DESCRIBE statement  
The DESCRIBE statement fetches select column list information for the prepared statement prepared by the  
PREPARE statement to the SQLDA structure. This type of statement is called an output DESCRIBE statement. The  
following is an example of the DESCRIBE statement that fetches select column list information for the prepared  
statement corresponding to SQL statement identifier STM2 to the SQLDA structure.  
If the prepared statement for SQL statement identifier STM2 is the statement shown below, the contents of the  
SQLDA structure are as follows:  
140  
7.2.1.1 Preparing and executing dynamic SELECT statements (for SQLDA stru  
cture)  
To fetch data continuously, prepare and execute a dynamic SELECT statement.  
An example of an application program that executes a dynamic SELECT statement using the SQLDA structure is  
shown in "Figure: Example of application program that executes a dynamic SELECT statement."  
Example:  
This is an example of a dynamic SELECT statement entered from a terminal.  
141  
[Figure: Example of application program that executes a dynamic SELECT statement]  
142  
143  
144  
7.2.1.2 Preparing and executing dynamic single row SELECT statements (for  
SQLDA structure)  
To fetch data from one row, prepare and execute a dynamic single row SELECT statement. To execute a dynamic  
single row SELECT statement, use an EXECUTE statement in which the USING clause is specified. An example of a  
dynamic single row SELECT statement using the SQLDA structure is shown below. For information about the SQL  
145  
statements that can use the SQL structure, see the examples in "Figure: Procedure for fetching execution results for a  
prepared statement."  
Example:  
This is an example of a dynamic single row SELECT statement entered from a terminal.  
7.2.2 Preparing SQL statements and manipulating the SQL descriptor area  
When data is fetched from a database using dynamic SQL, specify the target that will receive the execution results.  
For the specified target to be able to receive the execution results, the select column list information must match the  
attributes of the specified target. Matching is accomplished by fetching the select column list information from the  
database to the SQLDA structure, then modifying the attributes. The SQL descriptor area is positioned as shown in  
the figure below.  
146  
The SQL descriptor area either consists of only one identifier (COUNT) or consists of one identifier (COUNT) and one  
or more item descriptor areas (elements of the SQL descriptor area). COUNT indicates the number of select column  
lists in the SQL descriptor area; the data type is an exact numeric with binary precision. In the following figure, n  
indicates the number of select column lists in the SQL descriptor area.  
Each item descriptor area consists of the items listed in the table below.  
The table below shows the correspondence between the TYPE values (data-type codes) of the SQL descriptor area  
and SQL data types.  
147  
If the TYPE value is 1 or 12, the CHARACTER_SET_NAME value (character set name) varies according to character  
string type and national character string type.  
· Character string type: BASIC  
· National character string type: NCHAR  
If the TYPE value is 9, indicating the datetime type, the DATETIME_INTERVAL_CODE value is any of the codes listed  
in the table below.  
If the TYPE value is 10, indicating the interval type, the DATETIME_INTERVAL_CODE value is any of the following  
codes listed in the table below.  
148  
Procedure for fetching execution results  
The order in which execution results are fetched using the USING descriptor is shown in "Figure: Procedure for  
fetching execution results for a prepared statement." The flow of fetching execution results is explained here. Each  
SQL statement is explained after "Figure: Procedure for fetching execution results for a prepared statement."  
149  
[Figure: Procedure for fetching execution results for a prepared statement]  
When modifying an SQL statement dynamically to manipulate data, prepare execution of the SQL statement.  
Preparing the SQL statement  
Use the PREPARE statement to prepare a dynamic SQL statement for execution. To create and execute an SQL  
statement when the program is executed, store the SQL statement as a character string in a host variable called the  
SQL statement variable. Next, the PREPARE statement to prepare the SQL statement for execution. At this point, the  
SQL statement stored in the SQL statement variable is associated with the SQL statement identifier for statement  
identification.  
If the SQL statement variable is a preparable dynamic UPDATE statement (positioned) or a preparable dynamic  
DELETE statement (positioned), the corresponding cursor must be opened in advance. The specified cursor must  
also be a dynamic cursor.  
If the PREPARE statement is reexecuted for the defined SQL statement identifier, the allocated prepared statement is  
deallocated and a new prepared statement is prepared. If the allocated prepared statement is a dynamic SELECT  
statement, the statement prepared for a preparable dynamic UPDATE or DELETE statement (positioned) that  
150  
references the cursor corresponding to the SQL statement identifier is also deallocated. However, if the prepared  
statement is a dynamic SELECT statement, the cursor corresponding to the SQL statement identifier must have  
already been closed.  
An example of specifying the PREPARE statement is shown below. The SQL statement to be prepared for execution  
is assumed to be stored in SQL statement variable str. The SQL statement identifier of this SQL statement is defined  
as STM.  
Deallocating an SQL statement  
Use the DEALLOCATE PREPARE statement to deallocate the prepared statement prepared by the PREPARE  
statement.  
If the prepared statement is a dynamic SELECT statement, the statement prepared for a preparable dynamic  
UPDATE or DELETE statement (positioned) that references the cursor corresponding to the SQL statement identifier  
is also deallocated. In this case, the cursor must have already been closed.  
An example of specifying the DEALLOCATE PREPARE statement is shown below. The prepared statement for which  
the PREPARE statement defined the SQL statement identifier as STM is assumed to be the prepared statement that  
will be deallocated.  
SQL statements used to manipulate the SQL descriptor area  
As shown in "Figure: Procedure for fetching execution results for a prepared statement," use SQL statements to fetch  
select column list information from the database to the SQL descriptor area and to fetch the select column list  
information placed in the SQL descriptor area. The SQL statements used to manipulate the SQL descriptor area and  
their functions are explained below.  
ALLOCATE DESCRIPTOR statement  
The ALLOCATE DESCRIPTOR statement allocates the SQL descriptor area for the specified descriptor name. The  
allocated SQL descriptor area contains the number of item descriptor areas specified in this instance.  
An example of allocating the SQL descriptor area with descriptor name DESC1 is shown below. In this example, the  
number of item descriptor areas in the SQL descriptor area is assumed to be 200.  
DEALLOCATE DESCRIPTOR statement  
The DEALLOCATE DESCRIPTOR statement deallocates the SQL descriptor area allocated by the ALLOCATE  
DESCRIPTOR statement. If the descriptor name specified by the DEALLOCATE DESCRIPTOR statement is also  
151  
specified in a dynamic OPEN statement, the cursor must have already been closed.  
An example of deallocating the SQL descriptor area with descriptor name DESC1 is shown below.  
DESCRIBE statement  
The DESCRIBE statement fetches select column list information for the prepared statement prepared by the  
PREPARE statement to the SQL descriptor area. This type of statement is called an output DESCRIBE statement.  
The following is an example of the DESCRIBE statement that fetches select column list information for the prepared  
statement corresponding to SQL statement identifier STM2 to the SQL descriptor area.  
If the prepared statement for SQL statement identifier STM2 is the statement shown below, the contents of SQL  
descriptor area DESC2 are as follows:  
152  
GET DESCRIPTOR statement  
The GET DESCRIPTOR statement fetches the information set in the SQL descriptor area to a host variable. The data  
type of the host variable must match data type of the corresponding fetch identifier.  
An example of fetching the number of select column lists to host variable varcount is shown below.  
The following is an example of fetching the contents of the select column lists set in the SQL descriptor area to host  
variables vartypc, varleng, and varoctct. In this example, the values set in varwcount are assumed to be the element  
numbers of the item descriptor areas whose information is being fetched.  
When execution results are fetched from the SQL descriptor area, the data types of the host variables must  
correspond to TYPE, LENGTH, OCTET_LENGTH, PRECISION, SCALE, and CHARACTER_SET_NAME of the  
corresponding item descriptor area.  
An example of fetching the execution results set in the SQL descriptor area to host variable vardata is shown below. In  
this example, the value set in varwcount is assumed to be the element number of the item descriptor area whose  
information is to be fetched.  
SET DESCRIPTOR statement  
The SET DESCRIPTOR statement sets the data type of the target specification used to fetch execution results in the  
select column list of the SQL descriptor area. The data types of the host variables must correspond to the set  
identifiers. An example of changing the data type of the select column list of the SQL descriptor area is shown below.  
In this example, since CHARACTER(11) is assumed to be declared as the data type of host variable vardata, which  
receives the execution results, change the data type of the select column list to CHARACTER(10). Also in this  
example, the value set in varwcount is assumed to be the element number of the item descriptor area that will be set.  
153  
7.2.2.1 Preparing and executing dynamic SELECT statements (for SQL descri  
ptor area)  
To fetch data continuously, prepare and execute a dynamic SELECT statement. An example of an application program  
that executes a dynamic SELECT statement using the SQL descriptor area is shown in "Figure: Example of  
application program that executes a dynamic SELECT statement."  
Example:  
This is an example of a dynamic SELECT statement entered from a terminal.  
154  
[Figure: Example of application program that executes a dynamic SELECT statement]  
155  
156  
157  
7.2.2.2 Preparing and executing a dynamic single row SELECT statement (for  
SQL descriptor area)  
To fetch data from one row, prepare and execute a dynamic single row SELECT statement. To execute a dynamic  
single row SELECT statement, use an EXECUTE statement in which the USING clause is specified. An example of a  
dynamic single row SELECT statement using the SQL descriptor area is shown below. For information about the SQL  
statements that can use the SQL descriptor area, see the examples in "Figure: Procedure for fetching execution  
results for a prepared statement."  
Example:  
This is an example of a dynamic single row SELECT statement entered from a terminal.  
158  
7.3 Dynamically Modifying and Executing SQL Statement  
Conditions  
This section describes the following methods that can be used to dynamically modify and execute SQL statement  
conditions.  
· Manipulating the SQLDA structure  
- Preparing and executing dynamic SELECT statements (for SQLDA structure)  
- Preparing and executing dynamic single-row SELECT statements (for SQLDA structure)  
· Manipulating the SQL descriptor area  
- Preparing and executing dynamic SELECT statements (for SQL descriptor area)  
- Preparing and executing dynamic single-row SELECT statements (for SQL descriptor area)  
· Executing other prepared statements  
· Executing prepared statements for which variable attributes are known  
7.3.1 Manipulating the SQLDA structure  
To manipulate data after only dynamically modifying the SQL statement conditions, use dynamic parameter  
specifications. Store the values to be input in host variables. When the value of a host variable is set, the dynamic  
parameter specification information must match the attributes of the host variable. Fetch dynamic parameter  
specification information from the database to the SQLDA structure, modify the attributes, and specify the value. The  
following figure shows the position of the SQLDA structure.  
159  
The SQLDA structure holds dynamic parameter specification information.  
The SQLDA structure consists of item descriptor area SQLVAR, the maximum number of SQLVAR elements SQLN,  
and the number of effective elements SQLD.  
Item descriptor area SQLVAR consists of arrays in which dynamic parameter specification information is stored. SQLD  
indicates the number of dynamic parameter specifications in the SQLDA structure. The data type is exact numeric  
with binary precision.  
For more information about the SQLDA structure, see 7.2.1 Preparing SQL statements and manipulating the SQLDA  
structure."  
Procedure for setting dynamic parameter specification values  
"Figure: Procedure for setting dynamic parameter specification values for prepared statements" shows how to use the  
USING descriptor to set dynamic parameter specification values.  
160  
[Figure: Procedure for setting dynamic parameter specification values for prepared statement  
s]  
SQL statements used to manipulate the SQLDA structure  
As shown in "Figure: Procedure for setting dynamic parameter specification values for prepared statements," use SQL  
statements to fetch dynamic parameter specification information from the database to the SQLDA structure and to get  
dynamic parameter specification information from the SQLDA structure. The SQL statements used to manipulate the  
SQLDA structure and their functions are explained below.  
DESCRIBE statement  
The DESCRIBE statement fetches the dynamic parameter specification information for the prepared statement  
prepared by the PREPARE statement in the SQLDA structure. This type of statement is called an input DESCRIBE  
statement.  
An example of an input DESCRIBE statement that fetches dynamic parameter specification information for the  
prepared statement corresponding to SQL statement identifier STM1 to the SQLDA structure is shown below.  
161  
When the prepared statement corresponding to SQL statement identifier STM1 is as follows, the SQLDA structure  
variable will have the following contents:  
This section describes only the SQL statements used to manipulate a SQLDA structure containing dynamic parameter  
specification information. For information about other SQL statements, see "7.2.1 Preparing SQL statements and  
manipulating the SQLDA structure."  
7.3.1.1 Preparing and executing dynamic SELECT statements (for SQLDA stru  
cture)  
To fetch data continuously, prepare and execute a dynamic SELECT statement. "Figure: Example of an application  
program that uses the SQLDA structure (with dynamic parameter specification)" shows an example of an application  
program that uses the SQLDA structure to execute a dynamic SELECT statement with the dynamic parameter  
specification.  
Example:  
This is an example of a dynamic SELECT statement entered from a terminal. Data is searched using  
the value of the dynamic parameter specification entered from the terminal as the search condition,  
and the value is displayed.  
162  
[Figure: Example of an application program that uses the SQLDA structure (with dynamic pa  
rameter specification)]  
163  
164  
165  
166  
167  
7.3.1.2 Preparing and executing dynamic single-row SELECT statements (for  
SQLDA structure)  
To fetch single-row data, prepare and execute a dynamic single-row SELECT statement. To execute a dynamic  
168  
single-row SELECT statement, use the EXECUTE statement with the USING clause specified.  
An example of using the SQLDA structure to specify a dynamic single-row SELECT statement with the dynamic  
parameter specification is shown below. For information about the SQL statements used to manipulate the SQL  
structure, see "Figure: Procedure for setting dynamic parameter specification values for prepared statements."  
Example:  
This is an example of a dynamic single-row SELECT statement entered from a terminal. Data is  
searched using the value of the dynamic parameter specification entered from the terminal as the  
search condition, and the value is displayed.  
7.3.2 Using the SQL descriptor area  
To dynamically modify only the SQL statement condition and manipulate data, specify the dynamic parameter. Store  
values to be input in the host variable. When the host variable value is set, the dynamic parameter specification  
169  
information must match the host variable attributes. Matching is accomplished by fetching dynamic parameter  
specification information from the database to the SQL descriptor area, modifying the attributes, and then specifying  
the value. The following figure shows the position of the SQL descriptor area.  
The SQL descriptor area either consists of only one identifier (COUNT) or consists of one identifier (COUNT) and one  
or more item descriptor areas (elements of the SQL descriptor area). The data type is exact numeric with binary  
precision. In the following figure, n indicates the number of dynamic parameter specifications in the SQL descriptor  
area.  
For more information about the SQL descriptor area, see "7.2.2 Preparing SQL statements and manipulating the SQL  
descriptor area."  
Procedure for setting dynamic parameter specification values  
"Figure: Procedure for setting dynamic parameter specification values for prepared statements" shows how to use the  
USING descriptor to set dynamic parameter specification values.  
170  
[Figure: Procedure for setting dynamic parameter specification values for prepared statement  
s]  
SQL statements used to manipulate the SQL descriptor area  
As shown in "Figure: Procedure for setting dynamic parameter specification values for prepared statements," use SQL  
statements to fetch dynamic parameter specification information from the database to the SQL descriptor area and get  
dynamic parameter specification information placed in the SQL descriptor area. The SQL statements used to  
manipulate the SQL descriptor area and their functions are described below.  
DESCRIBE statement  
The DESCRIBE statement fetches the dynamic parameter specification information for the prepared statement  
prepared by the PREPARE statement in the SQL descriptor area. This type of statement is called an input DESCRIBE  
statement. When the descriptor name specified in the DESCRIBE statement is specified in the dynamic OPEN  
statement, the cursor must be closed.  
An example of the input DESCRIBE statement that fetches dynamic parameter specification information for the  
prepared statement corresponding to SQL statement identifier STM1 to the SQL descriptor area is shown below.  
171  
When the prepared statement corresponding to SQL statement identifier STM1 is as follows, SQL descriptor area  
DESC1 will have the following contents:  
GET DESCRIPTOR statement  
The GET DESCRIPTOR statement fetches information set in the SQL descriptor area to the host variable. The data  
type of the host variable must match the data type of the corresponding get identifier.  
An example of fetching the number of dynamic parameter specifications to host variable varcount is shown below.  
An example of fetching the contents of the dynamic parameter specification set in the SQL descriptor area to host  
variables vartype, varleng, and varoctet is shown below. The element number of the item descriptor area to be  
acquired should be the value set in varwcount.  
172  
SET DESCRIPTOR statement  
The SET DESCRIPTOR statement sets the data type and value of the dynamic parameter specification in the SQL  
descriptor area. Specify them using the constant or host variable. The data type of the host variable must match the  
data type of each set identifier. When the descriptor name specified in the SET DESCRIPTOR statement is specified  
in a dynamic OPEN statement, the cursor must be closed.  
An example of modifying the data type of the dynamic parameter specification in the SQL descriptor area and setting  
a value is shown below. In the example, the data type of host variable vardata in which the value to be set is stored is  
assume to be declared as CHARACTER(11). Change the data type of the dynamic parameter specification to  
CHARACTER(10). The element number of the item descriptor area to be set should be the value set in varwcount.  
This section describes only the SQL statements used to manipulate a SQL descriptor area containing dynamic  
parameter specification information. For information about other SQL statements, see "7.2.2 Preparing SQL  
statements and manipulating the SQL descriptor area."  
7.3.2.1 Preparing and executing dynamic SELECT statements (for SQL descri  
ptor area)  
To fetch data continuously, prepare and execute a dynamic SELECT statement. "Figure: Example of an application  
program that uses the SQL descriptor area (with dynamic parameter specification)" shows an example of an  
application program that uses the SQL descriptor area to execute a dynamic SELECT statement with the dynamic  
parameter specification.  
Example:  
This is an example of a dynamic SELECT statement entered from a terminal. Data is searched using  
the value of the dynamic parameter specification entered from the terminal as the search condition,  
and the value is displayed.  
173  
[Figure: Example of an application program that uses the SQL descriptor area (with dynamic  
parameter specification)]  
174  
175  
176  
177  
178  
179  
180  
181  
7.3.2.2 Preparing and executing dynamic single-row SELECT statements (for  
SQL descriptor area)  
To fetch single-row data, prepare and execute a dynamic single-row SELECT statement. To execute a dynamic  
single-row SELECT statement, use the EXECUTE statement with the USING clause specified. An example of using  
the SQL descriptor area to specify a dynamic single-row SELECT statement with the dynamic parameter specification  
182  
is shown below. For information about SQL statements used to manipulate the SQL descriptor area, see "Figure:  
Procedure for setting dynamic parameter specification values for prepared statements."  
Example:  
This is an example of a dynamic single-row SELECT statement entered from a terminal. Data is  
searched using the value of the dynamic parameter specification entered from the terminal as the  
search condition.  
7.3.3 Executing other prepared statements  
If a dynamic parameter specification is specified to perform data manipulations such as inserting or updating data, use  
an EXECUTE statement in which a USING clause is specified. The prepared statements that can be executed by an  
EXECUTE statement are preparable statements other than dynamic SELECT statements. For more details on  
statements that can be used for dynamic execution, see 7.1 "Overview of Dynamic SQL".  
If the SQL statement variable contains an UPDATE statement (positioned) or a DELETE statement (positioned), the  
corresponding cursor must be opened beforehand. Also, the specified cursor must be a dynamic cursor.  
An example of specifying an UPDATE statement (searched) with a dynamic parameter follows. Refer to the example  
in Figure: Example of an application program that uses the SQLDA structure (with dynamic parameter specification)  
for details on SQL statements for manipulating the SQL descriptor area.  
183  
Example:  
This is an example of an UPDATE statement (searched) entered from a terminal. Data for the values of  
the dynamic parameter specifications entered from the terminal is updated as a set clause.  
7.3.4 Executing prepared statements for which variable attributes are known  
When an application program is created, the attributes and the number of dynamic parameter specifications for  
prepared statements, or the attributes and the number of select column lists to be fetched may be known. If so, the  
SQL descriptor area need not be used and the values can be set or the execution results fetched by specifying  
USING arguments.  
To set the values of dynamic parameter specifications for a prepared statement by specifying USING arguments, the  
values to be set in the argument must be assigned. The number of dynamic parameter specifications in the prepared  
statement and the number of arguments must match. The data type of the dynamic parameter specification and that  
of the arguments must be comparable. To specify a null value, use an indicator variable.  
To fetch the execution results for the prepared statement by specifying USING arguments, specify an argument that  
will receive the execution results. The number of select column lists in the prepared statement and the number of  
arguments must match. The data type of the select column lists and that of the arguments must be comparable. For  
information about comparable data types, see Table "Comparable data types".  
Various specification examples follow.  
Using the EXECUTE statement to set the values of dynamic parameter speci  
fications  
Specify USING arguments in the EXECUTE statement to set the values of dynamic parameter specifications.  
Example 1:  
In this example, a row for which ITMNO is "400", PRODUCT is "VIDEO TAPE", and STOCKQTY is  
"500" is added to the STOCK table. The data types of ITMNO, PRODUCT, and STOCKQTY and those  
of the three arguments in which the values are to be set must be comparable.  
184  
Using the EXECUTE statement to fetch execution results  
Specify USING arguments in the EXECUTE statement to fetch execution results.  
Example 2:  
In this example, the values of PRODUCT and STOCKQTY for the product with ITMNO "110" are  
fetched from the STOCK table. The data types for PRODUCT and STOCKQTY and those for the two  
arguments in which the results are fetched must be comparable.  
Using the EXECUTE statement to set the values of dynamic parameter speci  
fications and to fetch execution results  
Specify USING arguments in the EXECUTE statement to set the values of dynamic parameter specifications and to  
fetch execution results.  
Example 3:  
In this example, the values of PRODUCT and STOCKQTY for the product with ITMNO "240" are  
fetched from the STOCK table. The data types of ITMNO and the argument in which the value is set,  
and the data types of PRODUCT and STOCKQTY and the two arguments in which the results are  
fetched must be comparable.  
185  
Using the dynamic FETCH statement to fetch execution results  
Specify USING arguments in the dynamic FETCH statement to fetch execution results.  
Example 4:  
In this example, values of PRODUCT and STOCKQTY with ITMNO greater than "200" are fetched  
from the STOCK table. The data types of ITMNO and STOCKQTY and those of the two arguments in  
which the results are fetched must be comparable.  
Using the dynamic OPEN statement to set the values of dynamic parameter  
specifications and the dynamic FETCH statement to fetch execution results  
Specify USING arguments in the dynamic OPEN statement and the dynamic FETCH statement to set the values of  
dynamic parameter specifications and fetch execution results.  
Example 5:  
In this example, values for PRODUCT with ITMNO between "140" and "240" inclusive are fetched from  
the STOCK table. The data types of ITMNO, the two arguments in which the values are set and the  
argument to which the result is fetched must be comparable.  
186  
Figure: Example of an application program that uses the USING argument is an example of an application program  
that uses the USING argument.  
Example 6:  
This is an example of a dynamic SELECT statement that has two dynamic parameter specifications.  
Values for ITMNO and STOCKQTY entered from a terminal are used as search conditions to fetch data.  
The rows that satisfy the search conditions are updated to the value of WHCODE entered from the  
terminal.  
187  
[Figure: Example of an application program that uses the USING argument]  
188  
7.4 Immediately Executing SQL Statements  
If an SQL statement is to be executed without being prepared, use the EXECUTE IMMEDIATE statement. The SQL  
statements that can be executed using an EXECUTE IMMEDIATE statement are preparable statements other than  
the dynamic SELECT statement and the single row SELECT statement. For details on preparable statements, see 7.1  
189  
"Overview of Dynamic SQL". When the preparable statement is the INSERT statement, UPDATE statement  
(searched), DELETE statement (searched), UPDATE statement (positioned), or DELETE statement (positioned), the  
dynamic parameter specification cannot be specified.  
If an SQL statement variable contains an UPDATE statement (positioned) or a DELETE statement (positioned), the  
corresponding cursor must be opened beforehand. Also, the specified cursor must be a dynamic cursor.  
The following is an example of specifying an EXECUTE IMMEDIATE statement.  
Example:  
The rows for which STOCKQTY is "0" are deleted from the STOCK table.  
7.5 Changing Database Names and Omitted Schema Name  
s
This section explains how to change database names and omitted schema names in prepared statements to be  
executed by dynamic SQL in an application program.  
7.5.1 Changing database names  
The database name for a prepared statement executed by dynamic SQL is the database name furnished by the  
connection. The furnished database name is the name of the SQL server name specified in the CONNECT statement.  
This database name can be changed in the application program. The database name specified in the SET CATALOG  
statement is for the prepared statements of PREPARE and EXECUTE IMMEDIATE statements. The changed  
database name is valid until the session terminates.  
The following is an example of specifying the SET CATALOG statement:  
Example:  
In this example, the name of the data base is changed to "RDBDB2".  
7.5.2 Changing schema names  
For more information on omitting schema name qualifiers when a table declaration is specified, see 6.10 "Omitting  
Schema Names". A schema name based on a table declaration cannot be omitted from the table name of a prepared  
statement executed by dynamic SQL. If the schema name is omitted from the table name of a prepared statement, the  
login name specified in the CONNECT statement becomes the schema name. The omitted schema name can be  
changed in the application program. The schema name specified in the SET SCHEMA statement is for the prepared  
statements of PREPARE and EXECUTE IMMEDIATE statements. The changed schema name is valid until the  
session terminates.  
The following is an example of specifying the SET SCHEMA statement:  
190  
Example:  
In this example, the schema name is changed to "SCH1".  
191  
192  
Appendix A SQL Data types and equivalent  
Host Variable Data Types  
Table: SQL data types and equivalent C language host variable data types lists SQL data types and the equivalent C  
language host variable data types.  
193  
[Table: SQL data types and equivalent C language host variable data types]  
194  
[Table: Time interval types and equivalent data types for C language host variables]  
Table: SQL data types and equivalent data types for COBOL host variables lists the data types and the equivalent  
data types for COBOL host variables.  
195  
[Table: SQL data types and equivalent data types for COBOL host variables]  
Table: Time interval types and equivalent data types for COBOL host variables shows the time interval types and  
equivalent data types for COBOL host variables.  
196  
[Table: Time interval types and equivalent data types for COBOL host variables]  
197  
198  
Appendix B Handling RDB Messages  
User handling of some messages can be referenced online. These messages are issued when RDB commands are  
being executed or an application program is being compiled. These messages also include messages set in a  
message variable (SQLMSG) of an application program.  
Use the online manual SymfoWARE/RDB Message Reference to reference user handling of messages issued by the  
SymfoWARE/RDB.  
This appendix describes reference methods for each task.  
When executing an RDB command  
Example 1:  
Display a description of the qdg03110u message issued by executing an RDB command.  
When compiling an application program (C language)  
Example 2:  
Display a description of message 11005 issued by the C precompiler.  
When compiling an application program (COBOL)  
Example 3:  
Display a description of message 11011 issued by the COBOL precompiler.  
199  
When executing an application program  
Example 4:  
Display a description of message JYP2031E set in the message variable (SQLMSG) when executing  
an application program.  
200  
Appendix C SQLSTATE Values  
The system reports the execution results of SQL statements in status variable SQLSTATE while an application  
program is running. The application program checks SQLSTATE whenever an SQL statement is to be executed, and  
processes SQL information while checking the result of SymfoWARE/RDB processing.  
SQLSTATE  
Status variable SQLSTATE is a 5-digit character-type variable. The value set in SQLSTATE, the result of an SQL  
statement being executed, is known as the status code. The status code is made up of two parts: a 2-digit exception  
code, followed by a 3-digit sub-exception code. Table: SQLSTATE values lists the code values for the execution  
results of SQL statements.  
[Table: SQLSTATE values]  
201  
202  
203  
204  
205  
206  
Glossary  
ALL set function  
The ALL set function is a set function for which ALL is specified in the argument. Rows containing the same value are  
targeted by the ALL set function.  
Related term: DISTINCT set function  
Application program  
In general, programs used in the work of a computer user are called application programs. In this manual, a program  
that reads data from a database and writes data to a database for user work processing is called an application  
program.  
Arbitrary character specifier  
In the LIKE predicate of an SQL data manipulation statement, the arbitrary character specifier specifies that a data  
position can contain any character. Related terms: escape character and arbitrary string specifier  
Arbitrary string specifier  
In the LIKE predicate of an SQL data manipulation statement, the arbitrary string specifier specifies that a data  
position can contain any character string of zero or more characters.  
Related terms: escape character and arbitrary character specifier  
Base table  
A table defined as a base table in logical structure definition. The data body is stored in a database space.  
BETWEEN predicate  
In a data manipulation SQL statement, the BETWEEN predicate is the search condition used to specify a range  
comparison in the format A BETWEEN B AND C (B?? A?? C).  
Related term: predicate  
Boolean operator  
Boolean operators AND, OR, and NOT are operators that specify logical operations in search conditions. In SQL  
search conditions, the logical operators logical conjunction (AND) or logical disjunction (OR) can be performed  
between predicates, or negation (NOT) can be performed within a predicate.  
207  
Column  
A constituent element of a table. A relational database represents data using two-dimensional tables consisting of  
rows and columns.  
Column name (item name)  
A column name is the name assigned to a column, defined in the schema definition. The column name is used in SQL  
statements for data manipulation to specify a column to be processed.  
Commit  
Makes the data manipulation of a transaction being processed take effect. The data manipulation in the transaction is  
physically reflected in the database. An application program deliberately controls a commit by issuing a COMMIT  
statement.  
Related term: rollback  
Comparison predicate  
The comparison predicate is a search condition specified using the comparison operators =, <, <=, >, >=, and <>.  
Connection  
Connection refers to the relationship that connects a client to a server. A connection is made by specifying a  
CONNECT statement in an application program. Conversely, a DISCONNECT statement cuts off the connection.  
Correlation name  
Correlation names are table names used by SQL statements for data manipulation to manipulate data. A correlation  
name is valid only for the SQL statement for which it is specified.  
Cursor  
A cursor is an indicator that indicates a row to be manipulated. A cursor is defined by a cursor declaration statement.  
The OPEN and CLOSE statements start and stop cursor operations, respectively. The FETCH statement moves the  
cursor.  
Cursor SQL statement  
A cursor SQL statement is a data manipulation SQL statement that uses a cursor to specify rows to be manipulated.  
Data manipulation SQL  
A data manipulation SQL is an SQL statement used to reference, add, delete, or update a data base.  
208  
Database name  
Many databases can be created as units of administration and design on one server system. To identify each  
database uniquely, each is assigned a unique name (database name) on the server system.  
DEFAULT clause  
An element of the definition of a column in a table. If the following condition applies, the value defined in the DEFAULT  
clause is inserted. The data to be inserted in a column is not specified when a row is inserted in a table using the  
INSERT statement. If a DEFAULT clause is not defined for a column, a NULL value is inserted.  
DELETE statement  
A data manipulation SQL statement used to delete a row from a table.  
DISTINCT set function  
The DISTINCT set function is a set function for which DISTINCT is specified in the set function argument. Rows  
containing the same values are not removed for a DISTINCT set function.  
Related term: ALL set function  
DS/90 7000 series  
A UNIX computer from Fujitsu Limited and PFU Limited that uses the SuperSPARC high-performance RISC  
processor.  
Dynamic SQL  
Dynamic SQL is a function for generating and executing SQL statements when executing an application program. In  
general, this function is used by general-purpose package programs.  
Embedded exception declaration  
An embedded exception declaration specifies the processing to be performed if an exception condition is issued when  
an SQL statement is executed in an application program. The WHENEVER statement is used to specify an embedded  
exception declaration.  
Embedded SQL  
SQL statements can be used as embedded SQL statements. Use embedded SQL statements to manipulate data in  
application programs written in high-level languages such as C language or COBOL. For example, when COBOL is  
used, specify an SQL statement embedded between EXEC SQL and END-EXEC.  
209  
Escape character  
An escape character is an alternate character for underscore (_) of an arbitrary character specifier or for percent  
symbol (%)of an arbitrary string specifier. An escape character is specified in a LIKE predicate. A LIKE predicate is  
used to specify a retrieval condition for data manipulation. However, to retrieve the actual underscore or percent  
symbol, an alternate character, the escape character, must be defined as the arbitrary character specifier for these  
symbols.  
Related term: arbitrary character specifier and arbitrary string specifier  
Exception condition  
When an SQL statement is executed in an application program, data to be processed may not be able to be found or  
an error may occur. Such a condition is known as an exception condition or exception. If an exception condition  
occurs while an application program is running, the status code corresponding to the exception condition is set in  
SQLSTATE.  
EXISTS predicate  
The EXISTS predicate specifies for an SQL statement the search condition that rows are manipulated depending on  
the result (true or false) of the specified subquery. The EXISTS predicate is specified in the EXISTS subquery.  
Related term: predicate  
Function  
The functions that can be used in SQL statements are set functions.  
Related term: set function  
Grouped column  
A grouped column is a column specified in the GROUP BY clause of a table expression for a query specification.  
Related term: grouped table  
Grouped table  
A grouped table is a table derived when a GROUP BY clause is specified in a table expression for a query  
specification. Rows containing the same grouped column value are grouped in a grouped table. Values that can be  
fetched from a grouped table are grouped column values or the values resulting from a set function being used for a  
group.  
Related term: grouped column  
Host variable  
A variable for passing data between an application program and a database in SQL statements that manipulate data.  
210  
IN predicate  
In the search condition specification for an SQL statement, the IN predicate specifies that rows are manipulated  
depending on comparisons with a set of values. The IN predicate is the predicate in IN (C, D, ...) or IN subquery.  
Related term: predicate  
Indicator variable  
In high-level languages such as C language and COBOL, the indicator variable is a variable specified to be paired  
with an SQL data variable. Indicator variables are used when SQL statements are used to fetch data from, and update  
a database. When SQL statements are used to update a database, the indicator variable indicates whether or not  
data stored in the SQL data variable contains a null value. When SQL statements are used to reference a database,  
the indicator variable indicates whether or not the execution result of the SQL statement has a null value stored in the  
SQL data variable. The indicator variable also shows the number of characters in character-string data stored in the  
SQL data variable.  
INSERT statement  
A data manipulation SQL statement used to add a row to a table.  
Join  
A type of relation operation. In a relational database, one table can be made from two or more tables. This operation  
is called join.  
LIKE predicate  
In the search condition specification for an SQL statement, the LIKE predicate specifies that rows are manipulated  
depending on a comparison of character type data. The LIKE predicate is the predicate in A LIKE B.  
Related term: predicate  
Logical structure  
This is one of the structures of a database along with the storage structure and physical structure. The data structure  
that includes the schema, table and column configuration, and column data types is called the logical structure.  
Constrains such as unique constrains, authorities, procedure routines, and triggers are also elements of the logical  
structure.  
Related terms: storage structure and physical structure  
Logical structure definition  
An element of SymfoWARE/RDB database definition (also called schema definition). Table and view table definitions  
apply to logical structure definition.  
211  
Non-cursor SQL statement  
An SQL statement used for data manipulation, the non-cursor SQL statement does not use a cursor to specify rows to  
be manipulated. Instead, the rows to be processed are specified in the search condition specified in the SQL  
statement.  
NOT NULL constraint  
The NOT NULL constraint is a constraint that can be applied to columns of tables. The NOT NULL constraint means  
that the value in a row of a column of a table cannot be a NULL value.  
Null (NULL)  
Null is the state in which the value in a row of a column of a table is undefined.  
NULL predicate  
In the search condition specification for an SQL statement, the NULL predicate specifies that rows are manipulated  
depending on whether the column value is null. The NULL predicate is the predicate in A IS NULL.  
Related term: predicate  
Parameter name  
A parameter name is the name of a parameter variable.  
Related term: Parameter variable  
Parameter variable  
Variable used to set an argument value when a procedure routine is called.  
Physical structure  
One of the database structures along with the logical structure and storage structure. The database space located on  
a magnetic disk volume is called the physical structure. A database space is an aggregate of fixed-size blocks.  
Related terms: storage structure and logical structure  
Physical structure definition  
An element of SymfoWARE/RDB database definition for creating a database space.  
Predicate  
A predicate is an element in SQL statements used to specify search conditions for rows to be manipulated. Seven  
212  
types of predicate are available: comparison, BETWEEN, IN, LIKE, NULL, quantified, and EXISTS. Search conditions  
are specified using a predicate and Boolean operators.  
Procedure routine  
A procedure routine defines processing procedures for a database using SQL.  
Procedure routine definition  
A procedure routine definition defines a procedure routine using a logical structure definition.  
Related term: Procedure routine  
Quantified predicate  
Using a quantified predicate is one method of specifying an SQL search condition. A quantified predicate uses a  
quantifier (ALL, SOME, or ANY) to specify the comparison of the object rows with a group of columns in the same  
column.  
Quantified value list  
A quantified value list is used when the IN predicate specified in the SQL statement search condition specifies rows to  
be manipulated based on comparison with a set of values. The quantified value list specifies the set of values to be  
compared by the IN predicate. For example, to specify rows for which the value in column C1 is 1, 3, or 5, specify C1  
IN (1, 3, 5). The part enclosed in parentheses is the quantified value list.  
Query expression  
A query expression is specified in a cursor declaration to derive a table. A query specification, or several query  
specifications joined by UNION, can be specified in a query expression.  
Query specification  
A query specification derives a table from an INSERT statement of an SQL statement or from a query expression. A  
query specification consists of a table expression that specifies the rows to be derived from the original table and a  
select column list that specifies the required columns from these rows.  
Read-only cursor  
In an SQL data manipulation statement, a cursor that cannot be used to update and delete is called a read-only cursor.  
The cursor declaration specifies whether a cursor is read-only. For example, if two or more tables are specified in the  
FROM clause of a query specification, the cursor is a read-only cursor.  
Related term: updatable cursor  
213  
Relation operation  
With relational databases, a relation operation allows only data from specified columns or a collection of data that  
meets certain conditions to be fetched. Three types of relation operations are available: selection, projection, and join.  
Selection refers to fetching rows that match specified conditions from a table. Projection refers to fetching only  
specified columns from a table. Join refers to specifying join conditions to create one table from two or more tables.  
Rollback  
Nullifying the data manipulation of a transaction that is being processed. The two kinds of rollback can be performed.  
Rollback in data manipulating SQL statement units can be the occurrence of an exception condition in a data  
operation. Rollback in transaction units can be the deliberate execution of a ROLLBACK statement by an application  
program.  
Related term: commit  
Routine name  
The name of a procedure routine.  
Related term: Procedure routine  
Row  
A row is one of the components of a table. In relational databases, data is expressed in terms of two-dimensional  
tables containing rows and columns.  
Row ID  
A row ID is used to identify a unique row of a table in the database. Users can manipulate the data of a desired row by  
using the row ID which is fetched by executing a single row SELECT statement or FETCH statement.  
Schema  
A constituent element of a database. SymfoWARE/RDB creates tables and view tables by analyzing data using  
information system analysis technique AA/BRMODELLING.  
Schema definition  
Schema definition is logical structure definition which defines the base tables and view tables that constitute a  
database, the column configuration of each table, and the attributes of each column. Authorities, procedure routines,  
and triggers can be defined as well as tables.  
Scope  
When using SQL statements for data manipulation to access a table, the table can be accessed within a specified  
data manipulation range. The specified data manipulation range is called a scope. The scope function can determine  
a data manipulation range for each user by applying/releasing a scope to/from users who access tables.  
214  
Search condition  
The specification for identifying rows that are the subject of the operation in data manipulation SQL. For example, a  
search condition is specified in the WHERE clause of an SQL SELECT statement.  
Select column list  
SQL statement query specifications and single row SELECT statements specify columns to be targeted for data  
reference from the table specified in the table expression. The columns to be targeted are specified in a select column  
list. Column names, set functions, and operation expressions can be specified in a select column list.  
Select target list  
A select target list is specified in a single row SELECT statement to fetch values to an application program from the  
columns specified in the select column list. The select target list follows INTO, and specifies host variables in a  
sequence corresponding to the sequence of columns.  
Sequence  
Function used to generate values that are unique in the system. Specifying a sequence in the SQL statement enables  
the generated value to be used. Its main use is to create the primary key values of a table.  
Session  
The period from the time an application program first accesses SymfoWARE/RDB to the time it terminates. The period  
from the time a connection is made until disconnection occurs constitutes one session.  
Set function  
In SQL, functions known as set functions can be used in data manipulation. Five set functions are available: COUNT,  
AVG, MAX, MIN, and SUM. The COUNT function calculates the row count for tables or columns. The AVG function  
calculates the average value for a column. The MAX function calculates the maximum value for a column. The MIN  
function calculates the minimum value for a column. The SUM function calculates the total for a column.  
Set operation  
In SQL, operations can be performed between groups of rows contained in two tables using a set operation. Three  
types of set operation are available: union, except, and intersect. Union refers to fetching all rows of both tables.  
Except refers to fetching rows contained in one table but not in the other. Intersect refers to fetching rows that contain  
the same value in both tables. Generally, if the result of a set operation is duplicate rows, the data is fetched as one  
row. However, if necessary, duplicate rows can be fetched as the result without any modification.  
215  
Single row SELECT statement  
The single row SELECT statement is an SQL data manipulation statement used to reference data. The single row  
SELECT statement can specify a search condition and fetch one row of data from a table.  
Sort specification  
When an SQL statement for manipulating data gets the results of a query expression using a cursor, it can rearrange  
the order of the rows. A sort specification specifies the column that is the sort key and ascending (ASC) or descending  
(DESC) order. A sort specification is specified in the ORDER BY clause of a cursor declaration.  
SQL  
SQL is a standard database language for performing database definition and data manipulation. RDB II basically  
conforms to the protocols of the international standards IS 9075 and ANSI X3.135.  
SQL embedded host program  
In an application program that manipulates a database, the parts of the program that perform database processing are  
written using SQL statements. The parts of the program that perform other kinds of processing are written using a  
programming language such as C language or COBOL. Application program in which SQL statements are embedded  
are called SQL embedded host programs. The following terms are used for application programs that contain  
embedded SQL statements:  
a. SQL embedded C program  
b. SQL embedded COBOL program  
SQL embedded host program is the generic term for a) and b).  
SQL variable  
A variable declared using a procedure routine.  
Related term: Procedure routine  
SQL variable name  
The name of an SQL variable.  
Related term: SQL variable  
SQLDA structure  
A user-defined descriptor area used to store dynamic parameter specification information and select list information  
contained in a prepared statement. The user sets the values of the dynamic parameter specifications and fetches the  
results of select lists via this area.  
Status variable  
The status variable, SQLSTATE, reports the processing result of an SQL statement to an application program. When  
216  
an SQL statement is executed, the status code for the execution result is stored in the status variable.  
Storage structure  
A database structure along with logical structure and physical structure. Storage structure physically locates data  
logically expressed as rows and columns of tables in a database as storage data. A storage structure is expressed  
using DSO and DSI. The storage structures for base tables are RANDOM structures and SEQUENTIAL structures;  
BTREE structures are the storage structures for indexes.  
Related terms: physical structure and logical structure  
Storage structure definition  
The definition of mappings between tables and database spaces. The two kinds of storage structure definition are  
data structure organization (DSO) definition and data structure instance (DSI) definition.  
Subquery  
A subquery is a specification used in an IN, comparison, quantified, or EXISTS predicate. The specification format is  
the same as that for a query specification. However, only one column must be specified for the derived table.  
Table  
In a relational database, data is represented using two-dimensional tables consisting of rows and columns. The two  
kinds of tables are base tables and view tables.  
Table declaration  
A table declaration declares the schema in which a table is located. If a table declaration is specified, the schema  
name need not be specified for a table in data manipulation statements. Using a table declaration simplifies the  
specification of table names. Using a table declaration also helps to make an application program independent of a  
data base.  
Table expression  
In single row SELECT statements and query specifications, the part that specifies the table, row, and group for which  
data is to be manipulated is the table expression. A table expression may contain the FROM, WHERE, GROUP BY,  
and HAVING clauses.  
Table name  
A name attached to a table. Table names are set in schema definitions. Table names are used to specify the tables to  
be made the subjects of operations in SQL statements that manipulate data.  
217  
Target specification  
A target specification is used to fetch values stored in a database to an application program. The target specification is  
specified as a variable.  
Trigger definition  
A trigger definition defines the data manipulation (insertion) of a table linked to the data manipulation (insertion,  
deletion, update) of another table.  
Unique constraint  
A constraint on a table or column. This constraint requires that a table cannot have multiple rows having the same  
value in a column or combination of columns.  
Updatable cursor  
In an SQL data manipulation statement, a cursor that can be used to update and delete is called an updatable cursor.  
The cursor declaration specifies whether a cursor is updatable. For example, if a table specified in the FROM clause  
of a query specification satisfies just one condition, the cursor is an updatable cursor.  
Related term: read-only cursor  
UPDATE statement  
The UPDATE statement is an SQL data manipulation statement used to update data in rows of tables.  
Value expression  
A value expression is a column specification, variable specification, set function, or literal, or a specification in which  
arithmetic operators (+, -, *, and /) are used to combine these items.  
Value specification  
A value specification, specified from an application program, specifies a value to be stored in, or compared with, a  
value stored in a data base. A value specification can be specified with a variable specification, literal (numeric,  
character-string, or national-language character-string), the keyword USER, or a dynamic parameter specification.  
WHERE clause  
In an SQL data manipulation statement, the WHERE clause is a search condition that specifies rows to be  
manipulated.  
218  

Mr Coffee Coffeemaker JWX9 User Manual
Motorola Timeport Phone User Manual
Motorola L6 GSM 1800 MHz User Manual
Microsoft Office 2010 Home and Student 79G 02020 User Manual
Meridian America G062 User Manual
Lenovo THINKCENTRE 8149 User Manual
Kambrook Computer Drive KEB433 User Manual
Cuisinart DGB 500C User Manual
Centon electronic DataStick Bolt 4GBDSB SILVER User Manual
Bunn Coffeemaker CDBCFP User Manual