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.
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 !