Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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,305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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 …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

688 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