Filter
Top Products
Chapter 2 Retrieving Data
This chapter describes the data manipulation that can be performed using the single row SELECT statement.
This chapter contains the following sections:
2.1 Fetching Column Values without Modifications
2.2 Fetching Columns that are Undefined
2.1 Fetching Column Values without Modifications
Use the single row SELECT statement to fetch one row of data from a table. The following is an example of the single
row SELECT statement:
Example:
In this example, STOCKQTY of the row for ITMNO 215 is obtained from the STOCK table.
In this example, host variable STOCKQTY is specified for the select target list in the single row SELECT statement.
Host variables are used to pass data between the application program and the database. That is, a host variable is
specified in an SQL statement to fetch data to be accessed by the application program from the database and to store
data specified by the application program in the database. An embedded SQL declaration clause is used to declare a
host variable. When a host variable is specified in an SQL statement, prefix the host variable with a colon. However,
for SQL statements that are not in C and for COBOL statements, specify host variables the same way as for general C
and COBOL variables without a colon prefixed. For C, national-language characters cannot be used for host variable
names.
This section explains the following operations that can be performed by the single row SELECT statement:
· Fetching values from multiple columns
· Fetching data that contains a null value
· Fetching data from multiple tables
· Deleting duplicate rows and fetching data
2.1.1 Fetching values from multiple columns
In the first example in 2.1 "Fetching Column Values without Modifications", only data from column STOCKQTY was
fetched. Suppose that data from two columns, STOCKQTY and WHCODE, is to be fetched. Obviously, the single row
SELECT statement could be executed twice. However, executing the statement more than once increases the number
of columns from which data is to be fetched and processing efficiency deteriorates. Instead, the single row SELECT
statement can be executed once to fetch values from multiple columns.
When fetching data from multiple columns, separate the column names in the select column list by commas. Specify
an equal number of host variables for storing the fetched data. Data from fetched columns is stored in host variables
in the same sequence from the left. Making the host variable names the same as the column names makes the
application program more understandable. However, note that values are not stored in the host variables with the
same names.
In the following example multiple columns are specified in a single row SELECT statement.
Example 1:
In this example, values for STOCKQTY and WHCODE for the product with ITMNO "215" are obtained
from the STOCK table. The values obtained for STOCKQTY and WHCODE are stored in host variables
STOCKQTY and WHCODE, respectively.
9