An expression refers to a column, a constant, a host variable, an SQL special register (for example, the USER special register), the SQL keyword NULL, a column function, a scalar function, an arithmetic expression, or any of these that can be connected by the concatenation operator. (The concatenation operator is discussed later in this chapter.) Using expressions, you can do calculations on data as part of a query. The calculations are performed before the data is returned to your program.
Figure 28 shows a simple arithmetic expression:
Figure 28. Breakdown of an Arithmetic Expression
|
|
There are four arithmetic operators that you can use:
* multiplication / division + addition - subtraction
Usually, the system reads an arithmetic expression from left to right, first applying any negations, then any multiplication or division operations, and then finally any additions and subtractions. For example, in the following expression:
BONUS - :MARKDOWN * .80
The system would take the value of the host variable MARKDOWN, multiply it by .80, and then subtract the result from the bonus.
You can change this order-of-precedence by using parentheses. For instance, if the above example were coded:
(BONUS - :MARKDOWN) * .80
The system would first subtract MARKDOWN from BONUS, and then multiply the result by .80. The two results would probably end up being quite different.
Host variables can be used in arithmetic expressions. For example:
PRICE * :QUANTITY + 1.44
As mentioned earlier, you must precede the names of host variables by a colon (:) to distinguish them from column names. That is, the following is interpreted as a host variable:
:PROJNO
The following, however, is interpreted as a column name:
PROJNO
Numeric constants can stand alone or be used in arithmetic combination with other constants or host variables or column names to form expressions. All three of the following are valid expressions:
200 -798.9768 PRICE * :QUANTITY + 1.44
Character constants cannot be used in arithmetic combinations, except when a character string representing a datetime value is used in datetime arithmetic. The following expression is valid:
| HIRE_DATE - '2000-01-01'
The following expression is not valid:
'FUDGE'*'GUMDROP'+'LEMON'
If you attempt to combine two pieces of data that do not have compatible data types with arithmetic operators, an error code is returned. The system performs data conversion on different types of data that are compatible.
Any of the following special registers can be used wherever an expression of the appropriate data type is used:
Using CURRENT DATE, TIME, and TIMESTAMP: The values of all datetime special registers in the same statement are based on the same time-of-day (TOD) clock reading.
In the examples below, one uses the select-statement and the other uses the UPDATE statement.
SELECT CURRENT DATE, PRSTDATE
FROM PROJECT
ORDER BY PRSTDATE
UPDATE PROJECT SET PRSTDATE = CURRENT DATE,
|PRENDATE = '2000-01-20'
WHERE PROJNAME = 'OPERATION'
Using CURRENT TIMEZONE: The CURRENT TIMEZONE is a signed time-duration containing the local time zone value. A negative value represents differentials west of the Greenwich-Mean-Time (GMT). A positive value represents differentials east of the GMT. CURRENT TIMEZONE can be used to convert local time into GMT by subtracting CURRENT TIMEZONE from local time. CURRENT TIMEZONE can be subtracted from a TIME or TIMESTAMP data type.
The following example shows a query that involves CURRENT TIMEZONE.
SELECT RECEIVED - CURRENT TIMEZONE
FROM IN_TRAY
Using CURRENT SERVER: This special register holds the server name of the application server currently connected. It has a CHAR(18) data type.
The following example shows a query that includes the CURRENT SERVER special register:
SELECT ID, INDATE, INTIME
FROM SAMP1
WHERE INRDB=CURRENT SERVER
Using USER: This special register is evaluated as the currently connected userid that is, the user ID of the person who is running the program, regardless of who preprocessed it. USER behaves exactly like a fixed-length character string constant of length 8, with trailing blanks if the user ID has fewer than eight characters.
Notes:
The following is a valid expression that includes the USER special register:
SELECT * FROM SYSTEM.SYSCATALOG WHERE CREATOR = USER
You can use the concatenation operator (CONCAT) to concatenate character strings or graphic strings. Long strings cannot be used with the concatenation operator.
The following example shows the concatenation of employees' last names and jobs, separated by a hyphen:
SELECT LASTNAME CONCAT '-' CONCAT JOB FROM EMPLOYEE
For a full description of this operation, including rules for character subtypes and CCSIDs, see the DB2 Server for VSE & VM SQL Reference manual.
| Note: | The || symbol is a synonym for CONCAT. Because the | symbol is not in a consistent position in all code pages, the use of || could impair code portability. |
As previously stated, host variables are host program variables that are declared in an SQL declare section. The host program can use these variables to interact with the database manager.
You can use host variables to pass data to or receive data from the database manager. Host variables used to contain column data or data used to evaluate an expression are called main variables. The data type and length attributes of a main variable depend on the data type and length of the column or expression to which the variable relates.
You can also use host variables to communicate information to and from the database manager about the contents of the main variable. If a host variable is used in this context, it is an indicator variable. Only use host variables that are declared with a data type equivalent to 15-bit integer as indicator variables. Refer to "Using Indicator Variables" for a description of their use.
Several SQL statements permit the use of host variables. Refer to the DB2 Server for VSE & VM SQL Reference manual for the syntax of these SQL statements. The syntax diagrams indicate whether host variables are permitted or required.
For a description of how to declare host variables, refer to the appropriate host language appendix.
A host structure is a special form of host variable. It is any two-level structure or substructure declared in an SQL declare section. Host structures can replace all or part of a host_variable_list. A host_variable_list can contain references to more than one host structure.
The elements of the host structure comprise the list of main variables in the host_variable_list. To provide indicator variable support for the elements of the host structure, you must use an indicator array. An indicator array of n elements provides indicator variable support for the first n elements of the host structure.
The elements of host structures and structures that contain host structures can replace scalar host variables in an SQL statement. You can qualify the element name with the names of parent structures and substructures. The following syntax diagram shows the format of a structure element reference.
>>-+----------------+--element_name---------------------------->< '-struct_name.---' |
It is only necessary to qualify a structure or element name where failure to do so would result in an ambiguous reference.
Elements of indicator arrays cannot be used as host variables and host structures (or structures that contain host structures) cannot be declared as arrays or contain arrays.
Refer to the appropriate host language appendix for rules on the declaration of host structures and indicator arrays. Refer to the DB2 Server for VSE & VM SQL Reference manual for more information on the use of host structures and indicator arrays in SQL statements.
Constants (also called literals) can be numeric or character data. They are fixed values that can be coded into SQL statements. Like host variables, they are used in various clauses in a number of different SQL statements.
The following example shows a character string constant coded in a WHERE clause:
DECLARE C CURSOR FOR SELECT * FROM EMPLOYEE WHERE LASTNAME = 'PEREZ'
Constants can be used in the SELECT clause to set up a new column in the result table, which has the specified constant in each of its occurrences. For example, the statement:
DECLARE C CURSOR FOR SELECT LASTNAME, 'WOW', 100.0 FROM EMPLOYEE WHERE COMM > 3200
would have the following result table:
LASTNAME EXPRESSION 1 EXPRESSION 2 ___________ _______________ _________________ LUCCHESI WOW 100.0 HAAS WOW 100.0 THOMPSON WOW 100.0 GEYER WOW 100.0 |
Integer constants consist of a number with an optional sign, such as -56, 103, or +786. (If you do not include a sign, the system assumes that the number is positive.) All integer constants are 4 bytes long; that is, there are no constants with a data type of SMALLINT.
Decimal constants consist of a number with a decimal point, such as 78.9687, -.00132, 64570., or +1672.80. If you do not supply a decimal point, the constant is interpreted as an integer. In storage, the number occupies a maximum of 16 bytes. Precision p, where 1 <= p <=31, is the total number of digits. Scale s, where 0 <= s <=p, is the number of those digits that are to the right of the decimal point. Leading and trailing zeros are included in both precision and scale. When the precision and scale are calculated, if the precision is greater than 31, leading zeros are removed until the precision is equal to 31. Trailing zeros are never removed. When decimal data values are multiplied or divided, an overflow condition may occur.
Consider the following:
a string of thirty one 9's. * 1.0
The string of 9's is treated as DECIMAL(31,0) and 1.0 as DECIMAL(2,1). The precision and scale of the product will then be 31 and 1 (DECIMAL(31,1)), respectively. This will result in a decimal overflow and an arithmetic exception will occur.
This decimal overflow, can be prevented by changing the constant '1.0' to '1.' This would define this constant as DECIMAL (1,0) and the resulting product as DECIMAL (31,0) instead of DECIMAL (31,1). If an expression contains decimal constants, you can influence its precision and scale by adding leading or trailing zeros to those constants.
A floating-point constant is an integer or a decimal constant followed by an exponent marked by the letter E. The E must be followed by an exponent. The 1E0 is acceptable and evaluates to 1. All these are permissible floating-point constants: -2E5, 2.2E-1, .2E6, +5E+2 or 4E0. All floating-point constants are double-precision in the system.
Character string constants are coded within quotation marks, and are varying-length character strings of letters, digits, or special characters, such as 'SMITH', '52', or 'k@r -5B'.
A character constant implicitly assumes either a FOR SBCS DATA or a FOR MIXED DATA attribute. You cannot assign the FOR BIT DATA attribute to a character constant. The constant is assumed to have a subtype of SBCS unless the following conditions are true. If the following conditions are true, the constant is assigned a subtype of mixed.
Mixed data is composed of a mix of SBCS and DBCS characters in one string. The DBCS portions of the string must be correctly formatted strings of DBCS characters. (For a discussion of the format and rules for using strings of DBCS characters, see Using a Double-Byte Character Set (DBCS).) An example of mixed data is:
'abc<(DEFG)>hi<(JKLM)>nop'
where abc, hi, and nop represent SBCS characters, and (DEFG) and (JKLM) represent DBCS characters.
To obtain a single quotation mark in a string of SBCS characters, you must code two consecutive single quotation marks. For example, the constant 'DON''T GO' is interpreted as DON'T GO. To obtain a single quotation mark in a string of DBCS characters, you only need to code a single quotation mark. Refer to the DB2 Server for VSE & VM SQL Reference manual for more information on mixed strings of SBCS and DBCS characters.
You can also code a character constant using its hexadecimal representation. Hexadecimal constants are treated like regular character constants. In DB2 Server for VM, hexadecimal constants are converted from the application requester default CCSID to the application server default CCSID before they are used.
The hexadecimal representation of a constant value must be enclosed within single quotation marks and preceded by an X. For example:
X'2D' X'C1C2C3C4' X'4256457D'
Each pair of hexadecimal numbers (0-9, A-F) represents a single byte. (Either uppercase or lowercase letters can be used.) Therefore, the number of hexadecimal numbers must be even and, when representing a DBCS character in a mixed constant, it must be a multiple of 4 (each DBCS character occupies 2 bytes in storage).
You can use hexadecimal constants to represent SBCS and mixed character data only. The maximum size for hexadecimal constants is 254 hexadecimal digits (that is, 127 SBCS characters or 63 DBCS characters).
The following is a valid expression using a hexadecimal constant:
LASTNAME CONCAT X'FF' CONCAT FIRSTNME
Graphic string constants are fully supported in COBOL and PL/I programs, but with different formats. The system supports three formats of the graphic constant: the SQL format and two PL/I formats.
The SQL format of the graphic constant is:
G'<(XXXX)>'
| Note: | N is a synonym for G. |
The G identifies the constant that follows as graphic; the <(XXXX)> is any valid string of DBCS characters, and the single quotation marks delimit the constant. You do not need to double the quotation marks in a graphic constant to obtain a single quotation mark. Use this format of the graphic constant in all situations except static SQL statements in PL/I programs.
The PL/I formats of the graphic constant are:
1. '<(XXXX)>'G 2. <(@'XXXX@'@G)>
| Note: | N is a synonym for G. |
Again, the G indicates that the constant is a graphic constant, and that the string bound by < and > must be a valid string of DBCS characters. In the second format, the single quotation marks and the G are within the string of DBCS characters; they are the DBCS format of the quotation mark and the G. In the second format, to obtain a single DBCS quotation mark, double the occurrence of the DBCS quotation mark within the string of DBCS characters. Use either of these formats of the graphic constant in static SQL statements in PL/I programs.
The PL/I preprocessor converts PL/I format graphic constants into SQL format graphic constants (G'<(XXXX)>') when they appear in SQL statements. This is done before passing the SQL statement to the application server for processing. Therefore, some DB2 Server for VSE & VM messages for incorrect syntax may refer to the SQL format of the constant, even though a PL/I format constant was coded in your program.
Graphic constants assume the default graphic CCSID. Subtypes do not apply to graphic data. For example, you cannot assign the FOR BIT DATA attribute to a graphic constant. For detailed information on CCSIDs and subtypes, see Using Character Subtypes and CCSIDs.
For information on the rules for the format and use of strings of DBCS characters with DB2 Server for VM, see Using a Double-Byte Character Set (DBCS).
A datetime constant is a character string constant or a decimal constant in a datetime context, as shown in the following examples:
| END_DATE - '1999-09-13' | | END_DATE - 10000101.
In the first example, |'1999-09-13' is a datetime character string constant; in the second, 10000101. is a decimal constant. A datetime decimal constant is a date duration, a time duration, or a timestamp duration. A date duration represents a number of years, months, and days, and is expressed as a DEC(8,0) number. A time duration represents a number of hours, minutes, and seconds, and is expressed as a DEC(6,0) number. A timestamp duration represents a number of years, months, days, hours, minutes, seconds, and microseconds, and is expressed as a DEC (20,6) number.
For more detailed information on date and time values, as well as durations, see Using Datetime Values with Durations.
Using indicator variables is optional in a host-variable reference. In
static SQL statements, indicator variables can be used to indicate that the corresponding host variables should be treated as null values or truncated values. Output indicator variables appear in the INTO clause of a SELECT or FETCH statement, and are associated with output that is passed from the database to the application program. Input indicator variables appear in the predicates of WHERE and HAVING clauses, in the SET clause of an UPDATE statement, with VALUES in an INSERT statement or in the SELECT clause, and are associated with input that is passed from the application program to the database.
Output indicator variables should always be used wherever null values are allowed in the database. Input indicator variables can be used to put null values into the database. They should, however, not be used in predicates unless there is a very good reason for doing so, because there may be a significant cost in performance.
Refer to the DB2 Server for VSE & VM SQL Reference manual for a description of the format of a host-variable reference that contains an indicator variable.
The following example illustrates the use of indicator variables.
SELECT FIRSTNME, LASTNAME INTO :FNME:FNMEIND, :LNME :LNMEIND FROM EMPLOYEE WHERE WORKDEPT = 'A00'
In this example, the indicator variable FNMEIND provides indicator variable support for the main variable FNME. The indicator variable LNMEIND provides indicator variable support for the main variable LNME.
The following notes on the use of indicator variables are grouped according to the type of indicator variable to which they apply.
When using input indicator variables, be aware of the following:
See the DB2 Server for VSE & VM SQL Reference manual for the different sets of rules for truth values for these predicates.
 |
This will return every row where MGRNO is null.
 |
If MGRIND has been set negative to make MGR null, the truth value is "UNKNOWN", and nothing will be returned.
Here is the pseudocode:
get either empno or lastname from user if empno is entered then empnoind = 0, else empnoind = -1 if lastname is entered then nameind = 0, else nameind = -1 SELECT * FROM EMPLOYEE WHERE EMPNO = :EMPNO:EMPNOIND OR LASTNAME = :NAME:NAMEIND
When using output indicator variables, be aware of the following:
Figure 29. Values Returned in Output Indicator Variables
| Value Returned | Meaning |
|---|---|
| 0 | Denotes that a non-null value that has been returned in the associated host variable is not null. |
| < 0 | Denotes that the value associated with the host variable is null, and should be treated exactly the same way as null column values. A -1 denotes that the null value resulted from a normal operation. A -2 denotes that the null value resulted from either a conversion error or an error while evaluating an arithmetic expression in an outer-select clause. |
| > 0 | Denotes that the system truncated the returned value in the associated
host variable because the host variable was not of sufficient length.
In addition, if the truncated item was a DBCS character or a string of DBCS characters, the indicator variable contains the length in characters before truncation. If the truncated item was a TIME value, truncated at its seconds part, the indicator variable contains the seconds. The SQLWARN1 warning flag in the SQLCA is set to 'W' whenever truncation occurs. |