Table of Contents

Search

  1. Preface
  2. Introduction to PowerExchange
  3. DBMOVER Configuration File
  4. Netport Jobs
  5. PowerExchange Message Logs and Destination Overrides
  6. SMF Statistics Logging and Reporting
  7. PowerExchange Security
  8. Secure Sockets Layer Support
  9. PowerExchange Alternative Network Security
  10. PowerExchange Nonrelational SQL
  11. PowerExchange Globalization
  12. Using the PowerExchange ODBC Drivers
  13. PowerExchange Datatypes and Conversion Matrix
  14. Appendix A: DTL__CAPXTIMESTAMP Time Stamps
  15. Appendix B: PowerExchange Glossary

SQL Syntax for a Basic Comparison

SQL Syntax for a Basic Comparison

In a basic comparison, a column is compared with either another column or a constant.
For example:
col1 = '123'
Complex expressions such as (col1 + col2) or (col1 * 3) are
not
supported.
The following table lists the valid operators:
Name
Operator
=
Equals
<>
Not equal
!=
Not equal
>
Greater than
>=
Not less than
<
Less than
<=
Not greater than
  • Character constants must be enclosed in single quotation marks and not exceed 255 characters in length.
  • Binary constants must be enclosed in single quotation marks and preceded by “X,” for example, X'00'.
  • Numeric constants can be specified with an optional leading sign and decimal point. Exponential floating point format is allowed.
Comparisons can be made only between operands of similar types:
  • A numeric operand can be compared only with another numeric operand.
  • A character operand can be compared with either a character or binary operand.
  • A binary operand can be compared with either a character or binary operand.
  • Date, time and timestamp (date-time) operands can be compared only with an identical column type or a character constant in the correct format.
Date-time constants must have the following formats:
  • DATE
    YYYY
    -
    MM
    -
    DD
    Single-digit
    DD
    and
    MM
    values are allowed. If the lower order portion of the date is omitted, it defaults to the values specified as the
    Data Defaults
    within the data map.
  • TIME
    HH
    .
    MI
    .
    SS
    Single-digit
    HH
    ,
    MI
    , and
    SS
    values are also allowed. If the lower order portion of the time is omitted it defaults to the following values:
    MI
    =00,
    SS
    =00.
  • TIMESTAMP
    YYYY
    -
    MM
    -
    DD
    HH
    .
    MI
    .
    SS
    .
    NNNNNN
    The
    NNNNNN
    value is the fractional seconds value. Single digits are allowed for
    DD
    ,
    MM
    ,
    HH
    ,
    MI
    ,
    SS
    , and
    NNNNNN
    . The date and time values default to the following values:
    MI
    =00,
    SS
    =00,
    HH
    =00,
    NNNNNN
    =000000.
  • You can enter only the date or only the time component with the other component defaulting. If insufficient information is available to deduce which component has been entered, it will be assumed to be the date component.
When character fields of different length are compared, the operands are made equal lengths by padding the shorter one with spaces, prior to the comparison being made.
Similarly for binary fields, when fields of different length are compared, the operands are made equal lengths by padding the shorter one with nulls (X'00'), prior to the comparison being made.
Comparisons involving character or binary columns are limited to columns less than 256 bytes in length.