Filter
Top Products
If fetched data has a null value and no indicator variable is specified, processing for the single row SELECT statement
ends in an error. If it is not known whether values for a column contain a null value, specify indicator variables.
2.1.3 Fetching data from multiple tables
A single row SELECT statement can be used to join two or more tables to fetch data. An example of joining two tables
follows. This section only explains the kinds of data that can be fetched as a result of joining tables. For information on
the rules for joining and manipulating tables, see Chapter 5 "Joining Multiple Tables and Manipulating Data".
Example:
In this example, STOCKQTY and warehouse location of the product with ITMNO "215" are obtained
from the STOCK table. Data about the warehouse location is contained in the WAREHOUSE table.
EQUIPMENT is the schema containing the WAREHOUSE table.
SELECT STOCKQTY, LOCATION INTO :STOCKQTY, :LOCATION
FROM STOCKS. STOCK, EQUIPMENT. WAREHOUSE
WHERE ITMNO = 215 AND STOCK. WHCODE = WAREHOUSE. WHCODE
[Figure: Example of joining two tables to fetch data]
2.1.4 Deleting duplicate rows and fetching data
A single row SELECT statement can be used to fetch only one row. An error occurs if a search condition specified in a
single row SELECT statement is true for more than one row. An error also occurs if search conditions are not specified.
If search conditions are not specified, data in all rows of the table is fetched. However, if when multiple rows are
fetched the values in all rows for all columns fetched are equal, the result can be placed in one row and fetched. To do
so, specify "DISTINCT". The following is an example of specifying DISTINCT:
Example 1
In this example, PRODUCT and WHCODE are obtained from the STOCK table for products for which
ITMNO is less than "120". The rows for which these values are the same are processed as one row.
SELECT DISTINCT PRODUCT, WHCODE INTO :PRODUCT, :WHCODE
12