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

Obtaining business hours based on start and end date

I have a transaction log that tracks issues from a call center. Each
time an issue is assigned to someone else, closed, etc. I get a time
stamp. I have these time stamps for the beginning of an issue to the
end of an issue and I'd like to determine how many business hours these
issues were open.

Issue BeginDt Enddt Total hours
1 3/29/05 5:00 PM 4/1/05 2:00 PM 69

Basically, this is the type of data I'm looking at and my hours of work
are from 7:30 - 5:00 weekdays. I need to come up with a way to remove
all nonbusiness hours, weekends, & holidays from the difference of the
two dates. Issues can span for 2-3 days or 20-30 days.

Please let me know if anyone has any ideas or has done something like
this before.

Thanks!On 1 Apr 2005 14:26:14 -0800, mitchchristensen@.gmail.com wrote:

>I have a transaction log that tracks issues from a call center. Each
>time an issue is assigned to someone else, closed, etc. I get a time
>stamp. I have these time stamps for the beginning of an issue to the
>end of an issue and I'd like to determine how many business hours these
>issues were open.
>Issue BeginDt Enddt Total hours
>1 3/29/05 5:00 PM 4/1/05 2:00 PM 69
>Basically, this is the type of data I'm looking at and my hours of work
>are from 7:30 - 5:00 weekdays. I need to come up with a way to remove
>all nonbusiness hours, weekends, & holidays from the difference of the
>two dates. Issues can span for 2-3 days or 20-30 days.
>Please let me know if anyone has any ideas or has done something like
>this before.
>Thanks!

Hi mitchchristensen,

The easiest way to do it is to use a calendar table. What that is, how
you can make it and various good ways to use it are described at Aaron's
site: http://www.aspfaq.com/show.asp?id=2519.

For this specific situation, I'd suggest the following approach:

DECLARE @.Start smalldatetime,
@.End smalldatetime
SET @.Start = '2005-03-22T17:00:00'
SET @.End = '2005-04-01T14:00:00'

SELECT DATEDIFF (minute, @.Start, @.End) / 60.0
- DATEDIFF (day, @.Start, @.End) * 14.5
- (SELECT COUNT(*)
FROM Calendar
WHERE dt > @.Start
AND dt < @.End
AND (isWeekday = 0 OR isHoliday = 1)) * 9.5

This might not be the quickes, but it has the advantage that it'spretty
straightforward: first, calculate the number of clock hours from start
to end; then subtract 14.5 hours (the time from 5:00 PM - 7:30 AM) for
each full day in the range; finally subtract another 9.5 hours (the time
from 7:30 AM to 5:00 PM) for each weekend or holiday in the range.

The assumption I made is that start and end dates will always be during
opening hours (i.e. not on weekends or on holidays and never outside the
7:30 AM - 5:00 PM range).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Hi mitchchristensen,
> The easiest way to do it is to use a calendar table. What that is, how
> you can make it and various good ways to use it are described at Aaron's
> site: http://www.aspfaq.com/show.asp?id=2519.
> For this specific situation, I'd suggest the following approach:
> DECLARE @.Start smalldatetime,
> @.End smalldatetime
> SET @.Start = '2005-03-22T17:00:00'
> SET @.End = '2005-04-01T14:00:00'
> SELECT DATEDIFF (minute, @.Start, @.End) / 60.0
> - DATEDIFF (day, @.Start, @.End) * 14.5
> - (SELECT COUNT(*)
> FROM Calendar
> WHERE dt > @.Start
> AND dt < @.End
> AND (isWeekday = 0 OR isHoliday = 1)) * 9.5

Since it seems unlikely that Mitch would like to disregard Christmas,
Thanksgiving and other holidays, Hugo solutions is very good. However,
I believe this is a solution that would work if we are for some reason
talking all Monday to Friday:

SELECT DATEDIFF (minute, @.start, @.stop) / 60.0 -
DATEDIFF (day, @.start, @.stop) * 14.5 -
DATEDIFF (week, @.start, @.stop) * 2 * 9.5

I owe Hugo's original query lot for this extension.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the suggestions!

Unfortunately I do need to disregard certain holidays based on the
region(I will have a holiday calendar for each region)

Also, end dates can occur on weekends or even holidays as some issues
are closed by the system based on a time parameter and the system has
no regards for whether or not it's an actual working day.

Mitch|||I have tried to do this in SQL and can tell you that you will be better
off doing it in application logic. The solution posted above performs
terribly.|||(mitchchristensen@.gmail.com) writes:
> Unfortunately I do need to disregard certain holidays based on the
> region(I will have a holiday calendar for each region)

Well, Hugo's solution should be your choice.

> Also, end dates can occur on weekends or even holidays as some issues
> are closed by the system based on a time parameter and the system has
> no regards for whether or not it's an actual working day.

Since I don't have any test data, I can't test Hugo's solution
for the case where the end date is a non a working day, but at a
glance it appers that his solution should handle this situation.

Since it was time, I repost Hugo's solution here:

DECLARE @.Start smalldatetime,
@.End smalldatetime
SET @.Start = '2005-03-22T17:00:00'
SET @.End = '2005-04-01T14:00:00'

SELECT DATEDIFF (minute, @.Start, @.End) / 60.0
- DATEDIFF (day, @.Start, @.End) * 14.5
- (SELECT COUNT(*)
FROM Calendar
WHERE dt > @.Start
AND dt < @.End
AND (isWeekday = 0 OR isHoliday = 1)) * 9.5

The complete thead can be reviewed at
http://groups.google.com/groups?dq=...0127.0.0.1%253E

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp