Filter
Top Products
[Figure: Example of specifying DISTINCT incorrectly]
2.2 Fetching Columns that are Undefined
This section explains the following operations that can be performed by the single row SELECT statement:
· Performing arithmetic operations on data
· Obtaining the total, average, maximum, and minimum values, and the row count for column values
2.2.1 Performing arithmetic operations on data
So far, this manual has only explained the fetching of column values from tables without modification. However, a
single row SELECT statement can also be used to fetch the results of arithmetic operations performed on column
values. This is achieved by specifying an operational expression in a select column list. The following is an example of
specifying an operational expression in a single row SELECT statement:
Example:
In this example, the result of adding 10 to STOCKQTY for the product with ITMNO "215" is fetched
from the STOCK table.
SELECT STOCKQTY + 10 INTO :STOCKQTY
FROM STOCKS. STOCK WHERE ITMNO = 215
The "+" operator is used for addition, the "-" operator is used for subtraction, the "*" operator is used for multiplication,
and the "/" operator is used for division. Also, a literal can be used to specify "10" as the value to be added to
STOCKQTY. Operations can be performed on host variables and among columns as well as on columns and literals.
When the single row SELECT statement in the preceding example is executed, "15" is stored in host variable
STOCKQTY.
Specifying an operational expression
Monadic and dyadic operators can be used in operational expressions.
Monadic operators
The following are the two monadic operators:
+:
No effect
-:
Sign inversion
14