15.4.2 Supported expressions
The PFM ODBC driver supports the use of scalar operation expressions from only those in the selection list of the SELECT statement. Operation expressions and subqueries are not supported in search conditions such as the WHERE clause. Follow the standard rules for operator precedence, expression syntax, and the use of parentheses except for the following points:
-
A string of literal characters cannot contain double quotation marks (").
-
In a common key field, only the AND operator can be used. For details on common key fields, see 15.4.4 Specifying common key fields.
-
Operation order is not guaranteed among connection clauses that have the same priority. If you give a condition expression in which the left-to-right ordering does not hold, clarify priority by using parentheses (( )). For reference, note the following examples.
- Examples:
-
A and B and C
In this case there is no problem because the left-to-right ordering holds.
A or B and C
In these cases, the left-to-right ordering does not hold so you have to provide a clearer definition: A or (B and C) or (A or B) and C.
- Organization of this subsection
(1) Type conversion
The PFM ODBC driver supports all of the standard type conversions. Internally, a numerical type is one of the following types:
-
SQL_TINYINT (1-byte signed integer)
-
SQL_SMALLINT (2-byte signed integer)
-
SQL_INTEGER (4-byte signed integer)
-
SQL_DOUBLE (8-byte floating point number)
The type of each column is defined in the data dictionary of the ODBC Driver. For example, the SQL_SMALLINT-type column can be added to the SQL_DOUBLE-type column. The resulting column from additions is the smallest type to store the result, which in this case is the SQL_DOUBLE column.
(2) Values of date, time, and time-stamp
The PFM ODBC driver supports expressions including the following values, which indicate date, time, and time-stamp.
-
SQL_DATE
-
SQL_TIME
-
SQL_TIMESTAMP
When using these values, the following rules are applied:
-
Addition (+) and subtraction (-) can be used.
-
The SQL_DATE column is represented as the number of days. For example, adding 3 to the SQL_DATE column results in incrementing the date by 3 days.
-
The SQL_ TIME and SQL_TIMESTAMP columns are represented as the number of seconds. For example, adding 3 to the SQL_TIME column results in incrementing the value by 3 seconds. The following are reference time values:
-
SQL_TIME: 0:00 a.m.
-
SQL_TIMESTAMP: 1970-01-01 at 0:00
-
-
The SQL_TIME column can only be added to the SQL_DATE column. Also, the SQL_DATE column can only be added to the SQL_TIME column. The addition result column is the SQL_TIMESTAMP column. This feature comes in useful when you perform an operation that can affect both time and date. Note that other operations that relate to the date, time, and time-stamp columns are not supported on either side of an expression operator.
-
Specify 0:00 a.m. as 00:00:00. Also, specify 0:00 p.m. as 12:00:00.
-
When you specify the DATE value, TIME value, or DATETIME value in the WHERE clause, escape sequences and literal characters must be specified in the following format: Specify character strings in the YYYY/MM/DD hh:mm:ss format. For details on how to specify the names of columns and tables, see 15.4.3 Specification rules for the names of columns and tables.
- Escape sequence:
-
WHERE NT_PI_TCP.PI_TCP_DATETIME = {ts 'YYYY-MM-DD hh:mm:ss'}
WHERE NT_PI_TCP.PI_TCP_DATE = {d 'YYYY-MM-DD'}
WHERE NT_PI_TCP.PI_TCP_TIME = {t 'hh:mm:ss'}
- String of literal characters:
-
WHERE NT_PI_TCP.PI_TCP_DATETIME > 'YYYY/MM/DD hh:mm:ss'
WHERE NT_PI_TCP.PI_TCP_DATE = 'YYYY/MM/DD'
WHERE NT_PI_TCP.PI_TCP_TIME = 'hh:mm:ss'