Informix Error -25805: The interval value must be a numeric or INTERVAL data type.
Cause and resolution
The interval value must be a numeric or INTERVAL data type.
The resultant data type of the expression for an interval value must satisfy one of these two sets of conditions: <1> If the fragment key is a numeric type, the expression must return a built-in numeric data type. This cannot be a MONEY data type, and it cannot have a fractional part.
<2> If the fragment key is a DATE or DATETIME type, the expression must return an INTERVAL data type whose last field qualifier cannot be a FRACTION.
The following statements generate an error:
CREATE TABLE address (street VARCHAR(128), city VARCHAR(32), state CHAR(2), zip INT) FRAGMENT BY RANGE(zip) INTERVAL("123") STORE IN (dbs1, dbs2, dbs3) PARTITION p1 VALUES < 10000 IN dbs3;
The interval value "123" is not a numeric or INTERVAL data type.
CREATE INDEX add_zip_idx ON address(zip) FRAGMENT BY RANGE(zip) INTERVAL(1000.20) STORE IN (dbs1, dbs2, dbs3) PARTITION p1 VALUES < 10000 IN dbs3;
The interval value 1000.20 has a fractional part.
The following statement is correct:
CREATE INDEX add_zip_idx ON address(zip) FRAGMENT BY RANGE(zip) INTERVAL(100+900) STORE IN (dbs1, dbs2, dbs3) PARTITION p1 VALUES < 10000 IN dbs3;
The interval value (100+900)is an expression whose result is numeric without a fractional part.
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-25805 fires when the INTERVAL(...) expression's resultant type doesn't match its
fragment key's type — per the official guidance: for a numeric fragment key, the
expression must be a non-MONEY, non-fractional built-in numeric type; for a DATE/
DATETIME fragment key, it must be an INTERVAL type whose last field qualifier isn't
FRACTION.
- A non-numeric, non-
INTERVALinterval-value expression, per the official guidance — the first named case. - A numeric interval value with a fractional part, per the official guidance — the second named case.
Solutions / Resolution
- Match the interval value's type to the fragment key's type, per the official
guidance — numeric for a numeric key,
INTERVALforDATE/DATETIME.
Examples
A string interval value
CREATE TABLE address (street VARCHAR(128), city VARCHAR(32), state CHAR(2), zip INT)
FRAGMENT BY RANGE(zip) INTERVAL("123") STORE IN (dbs1, dbs2, dbs3)
PARTITION p1 VALUES < 10000 IN dbs3;
-- -25805: "123" isn't a numeric or INTERVAL type
A fractional interval value
CREATE INDEX add_zip_idx ON address(zip)
FRAGMENT BY RANGE(zip) INTERVAL(1000.20) STORE IN (dbs1, dbs2, dbs3)
PARTITION p1 VALUES < 10000 IN dbs3;
-- -25805: 1000.20 has a fractional part
Diagnostic Checks
- Check the interval value's data type and precision, and match it to the fragment key's type requirements.
Related Errors / Related Topics
- -25804 — "The interval value must be a non-zero positive constant." A related interval-value error, on sign/constancy rather than type.
- -25806 — "The fragment key for interval fragmentation must be a numeric, DATE, or DATETIME type." A related interval-fragmentation restriction, on the fragment key itself.
- -25810 — "The fragment key, interval value, and upper limit of the fragments are not compatible." A broader related type-compatibility error.
The INTERVAL(...) value's type didn't match the fragment key's requirements — use a
matching numeric or INTERVAL type.