CODEWITHSUNDEEP
sql-server
Jefferey Cave
Jefferey Cave

Reputation: 2899

SQL Cast TinyInt with Case Statement

I am writing a query that aggregates some commonly reported on data. Basically, I'm trying to determine when a resource has been used.

Unfortunately, my query is returning an error:

Arithmetic overflow error converting expression to data type tinyint.

insert into Utilization
select resource
       ,timesegment
       ,case when min(uc.casemain_id) is null then 0 else count(uc.casemain_id) end as InUse
from ...
group by resource, timesegment

The error is occurring on InUse and the targeted column is a tiny int. I have run the query independently, and the min and max values of the count are 0 and 4.

select min(InUse), max(InUse)
from (... the query above ...)

returns: 0, 4

Any suggestions on why this is happening?

Updates

Sample of combination that causes the error: 

CREATE TABLE Usage(
    [Resource] [varchar](50) NULL,
    [TimeSegment] [datetime] NULL,
    [InUse] [tinyint] NULL
);

CREATE TABLE TimeMap(
    [TimeSegment] [datetime] NULL,
    [Resource] [varchar](50) NULL,
    [Case] [varchar](50) null,
    [Usage] [tinyint] NULL
);

CREATE PROCEDURE [dbo].[BuildTimeUsageMap] AS
BEGIN
    truncate table Utilization;
    insert into Utilization
    select resource
           ,timesegment
           ,count(uc.casemain_id) as InUse
    from   timemap
    group by resource, timesegment
END;

exec BuildTimeUsageMap;

I have altered the select statement to be simplified (changed count(*) to count(uc.casemain_id) does return a count of non-null values, thank-you @ughai ). Also I have resolved the issue by changing the data type of InUse to a smallint.

While changing the datatype to smallint makes the problem go away, it does not explain why it doesn't work in the first place.

select min(InUse), max(InUse) from Utilization

results: 0,4

Both of which should fit in a tinyint

Upvotes: 1

Views: 2285

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

I believe that you're falling foul of SQL Server's illogical errors (where it performs certain actions in a different order to the logical processing order for SQL statements and this raises errors that would not have been raised had it followed the logical order).

In such a circumstance, your only real option is to split the query into multiple queries. Note that subqueries/CTEs aren't enough to guarantee that the optimizer won't move things around sufficiently to cause these errors.

So I would:

a) Create a temp table with looser type constraints than the real table (i.e. uses int or bigint rather than tinyint) and populate it. This query should be the one that performs any aggregations and any filters.

b) (Optional) run a query looking, specifically for out-of-range values and raise specific error messages for any found

c) INSERT from the temp table into the real table.

Upvotes: 2

Karthik Kola
Karthik Kola

Reputation: 93

Try this :

CAST( case when min(uc.casemain_id) is null then 0 else count(uc.casemain_id) end AS TINYINT )

Upvotes: 1

Jason W
Jason W

Reputation: 13209

Assuming that InUse is a TINYINT, you probably have more than 255 records in your query, so the overflow is the COUNT(uc.casemain_id) returning a value higher than 255. The MIN and MAX are not counting records - just highest and lowest values within your query. Replace your second query with COUNT(1) keeping the same group by to confirm the row count exceeding the TINYINT max size.

Upvotes: 0

Related Questions