Interval Error

Database
Enthusiast

Interval Error

The code snippet below is in a view. In SQL Assistant when I display the columns it says that the Date_group_id is an integer datatype.

SELECT
CAST(
(CAST ((CAST( (CAST (End_Dt AS FORMAT 'YYYYMM')) AS CHAR(6))||'01') AS date FORMAT 'YYYYMMDD') -
CAST ((CAST( (CAST ((Start_Dt) AS FORMAT 'YYYYMM')) AS CHAR(6))||'01') AS date FORMAT 'YYYYMMDD')
MONTH(4) )
AS SMALLINT) + 1 AS Date_Group_Id
FROM
TABLES

When I do the following:
Select count(*) from ViewName
Or if I run
Select count(*), date_group_id from ViewName group by date_group_id

I have no problem
If I try
Select count(*) from ViewName where date_group_id < 12

I get
7453: Interval field overflow.

I have other views coded similarly but the end_dt value is from a different column - none of the other views experience the same issue.

Any ideas?

John P

1 REPLY
Enthusiast

Re: Interval Error

This is sort of a funny situation, I don't pretend to know the answer, but I will explain my findings.

First thing would be to do an EXPLAIN on these SQLs and you would probably have a shock to see all that difference.

The plain select won't display all those CAST operations in it's steps, where as all those would be present in the one with where clause.

This makes me think that in the plain select the operation is performed as "INTEGER"s (some where on - the - fly not on spool) where as the one with where condition results in the operation being performed as "SMALLINT" (and is happening in the spool) which is causing an overflow some where (Which is not happening with INTEGERs)

... This is one of those software mysteries ... or may be qualifies for a Bug !