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

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

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

· 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:

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.

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:

[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.

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:

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.

[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.

[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.

[Figure: Relationship between STOCK table, ORDER table, and COMPANY table]

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.

[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

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]

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

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

[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

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.

[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.

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]

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.

[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.

[Table: Data type of columns targeted by set functions and data type of the results]

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.

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.

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.

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

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.

[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.

[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.

[Figure: Adding data using a complex query specification]

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

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.

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.

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.

[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

[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.

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.

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.

[Figure: Example of deleting all rows from a table]

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.

[Figure: Cursor overview]

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

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.

· 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]

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.

[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.

[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.

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:

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.

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".

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.

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]

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

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.

[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.

[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.

[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.

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

[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

[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

[Figure: Example in which multiple tables are joined and data manipulated]

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

fetched.

[Figure: Example in which multiple tables are joined using a join table]

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

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.

[Figure: Example of joining a table to itself and manipulating data]

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

[Figure: Example of performing an operation on different rows]

5.5 Obtaining the Aggregate for Rows from Multiple Table

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.

[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.

[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].

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.

[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.

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.

[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

[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.

[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.

[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.

[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.

Figure: Example of a table derived from a HAVING clause shows the table derived from the HAVING clause and the

query specification result.

[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,

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]

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.

[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.

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

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]

[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.

[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".

[Table: Comparable data types]

The following is an example of specifying a comparison predicate:

Example 1:

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

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.

[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)

[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.

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.

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.

[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.

[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.

[Figure: Example of specifying a quantified predicate]

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.

[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

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

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

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

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

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