Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

TSQL CASE Statement Error SQLServer 7(Msg 8115, "Arithmetic overflow error converting numeric to data type numeric.")

Posted on 2003-11-28
2
Medium Priority
?
1,308 Views
Last Modified: 2011-09-20
SQLServer 7
Watch out for this one…

Statement Below Works
Select
CASE d.AmPmAll
WHEN 1 THEN 0.5
WHEN 2 THEN 0.5
WHEN 3 THEN 1.0 --CORRECT
END TimeOfDayNum
from  Dayoff d


Statement Below Fails
Select
CASE d.AmPmAll
WHEN 1 THEN 0.5
WHEN 2 THEN 0.5
WHEN 3 THEN 1 --INCORRECT
END TimeOfDayNum
from  Dayoff d

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

The ".0" makes a big difference eh!

FYI - d.AmPmAll is a tinyint field; Posted to save someone else time.
0
Comment
Question by:GJPerkins
  • 2
2 Comments
 
LVL 6

Accepted Solution

by:
lausz earned 60 total points
ID: 9837244
The problem is that you have to manage the same data type for all the case statement ..is for this taht you need to use 1.0-

0
 
LVL 6

Expert Comment

by:lausz
ID: 9837263
I tested your sample in sql 2000 and it works in the two ways. I don't know in 7.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question