OCI-22053: overflow error

I have a report that runs the following in it's query: " (SELECT cast(d
.ACT_DT as TIMESTAMP)
FROM GPROD.VSL_ACT d
WHERE d .ses_num = gprod.vsl_act.ses_num
AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
AND
ROWNUM <= 1 AND d
.ACT_TYP_TXT = 'DEPS') - CAST(act_dt AS TIMESTAMP) ElpsTime,
(to_date(to_char((SELECT cast(d .ACT_DT as TIMESTAMP)
FROM GPROD.VSL_ACT d
WHERE d .ses_num = gprod.vsl_act.ses_num
AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
AND
ROWNUM <= 1 AND d
.ACT_TYP_TXT = 'DEPS'),'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
hh24:mi:ss')-to_date(to_char(act_dt,'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
hh24:mi:ss')) * 24 * 60 * 60 sum_seconds"
Basically, this takes two date fields, subtracting one from the other. It
works 99% of the time, but for some records, it get #ERROR in this calculated
field. I look at the data, and it makes no sense because I get the error
when the difference could be 10 seconds, or 3 minutes, or whatever. I don't
see any logic to it. The database is an Oracle 10g database. I am using VS
2005 sp1. I have created a new project as per a previous post, but still get
the error. Here is the full error:
The data set â'dsShuntâ' contains a definition for the Field â'SUM_SECONDSâ'.
The data extension returned an error during reading the field.
System.Data.OracleClient.OracleException: OCI-22053: overflow error
Thanks for any help.
Darryl.On Apr 11, 7:14 am, Darryl <Dar...@.discussions.microsoft.com> wrote:
> I have a report that runs the following in it's query: " (SELECT cast(d
> .ACT_DT as TIMESTAMP)
> FROM GPROD.VSL_ACT d
> WHERE d .ses_num = gprod.vsl_act.ses_num
> AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
> AND
> ROWNUM <= 1 AND d
> .ACT_TYP_TXT = 'DEPS') - CAST(act_dt AS TIMESTAMP) ElpsTime,
> (to_date(to_char((SELECT cast(d .ACT_DT as TIMESTAMP)
> FROM GPROD.VSL_ACT d
> WHERE d .ses_num = gprod.vsl_act.ses_num
> AND d .seq_num = gprod.vsl_act.seq_num AND d .act_dt > gprod.vsl_act.act_dt
> AND
> ROWNUM <= 1 AND d
> .ACT_TYP_TXT = 'DEPS'),'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
> hh24:mi:ss')-to_date(to_char(act_dt,'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy
> hh24:mi:ss')) * 24 * 60 * 60 sum_seconds"
> Basically, this takes two date fields, subtracting one from the other. It
> works 99% of the time, but for some records, it get #ERROR in this calculated
> field. I look at the data, and it makes no sense because I get the error
> when the difference could be 10 seconds, or 3 minutes, or whatever. I don't
> see any logic to it. The database is an Oracle 10g database. I am using VS
> 2005 sp1. I have created a new project as per a previous post, but still get
> the error. Here is the full error:
> The data set 'dsShunt' contains a definition for the Field 'SUM_SECONDS'.
> The data extension returned an error during reading the field.
> System.Data.OracleClient.OracleException: OCI-22053: overflow error
> Thanks for any help.
> Darryl.
This link might provide some insight:
http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/185b831ab7a267b0/c0106784db18d83f?lnk=st&q=System.Data.OracleClient.OracleException%3A+OCI-22053%3A+overflow+error&rnum=3#c0106784db18d83f
Regards,
Enrique Martinez
Sr. Software Consultant