Variable type - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Variable type

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2012-06-20 : 11:54:47

I want to define the following variable and define a format for it:

,Employee = case
when ..

else
end

where should I define the format for example float for Employee.
Cuz sinse I have not defined any format for it, it goes fine through crating the view but displaying that view I get the following error:

Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting varchar to data type numeric.

thx

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-06-20 : 12:07:08

Post your whole case statement, you need to convert something, but we can't tell you what without seeing the statement.

goligol
Posting Yak Master

128 Posts

Posted - 2012-06-20 : 12:22:41

ALTER VIEW [dev].[CBP_BLS_2009]
as

SELECT [fipstate]
,[fipscty]
,[naics]
,["empflag"]
,["emp_nf"]
,["emp"]
,["qp1_nf"]
,["qp1"]
,["ap_nf"]
,["ap"]
,["est"]
,employees = case
when ["emp"] ='0' then (["n1_4"]*2.5 + ["n5_9"]*7 + ["n10_19"]*15 + ["n20_49"]*35 + ["n50_99"]*75 + ["n100_249"]*175 + ["n250_499"]*375 +
["n500_999"]*750 + ["n1000"]*1000 + ["n1000_1"]*1000 + ["n1000_2"]*1000 + ["n1000_3"]*1000 + ["n1000_4"]*1000)
else ["emp"]
end
,["n1_4"]
,["n5_9"]
,["n10_19"]
,["n20_49"]
,["n50_99"]
,["n100_249"]
,["n250_499"]
,["n500_999"]
,["n1000"]
,["n1000_1"]
,["n1000_2"]
,["n1000_3"]
,["n1000_4"]
,["censtate"]
,["cencty"]
FROM [trade].[dev].[CountyBusinessPattern_2009]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-20 : 12:34:13

is EMP a string or a number? Your case statement can't return two different data types.

Jim

Everyday I learn something that somebody else already knew

goligol
Posting Yak Master

128 Posts

Posted - 2012-06-20 : 13:40:54

EMP is a number.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-20 : 14:06:36

Strange that EMP is a number but you refer to it as a string "when ["emp"] ='0' then " If EMP really is a numeric data type and not just something that looks like a number, then one of the columns you are doing the math on must not be numeric. Could you provide the DDL for [trade].[dev].[CountyBusinessPattern_2009] ?

Jim

Everyday I learn something that somebody else already knew

goligol
Posting Yak Master

128 Posts

Posted - 2012-06-20 : 16:32:15

How should I creat DDL:-(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-20 : 19:45:17

just script out table as create from SSMS and post it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

goligol
Posting Yak Master

128 Posts

Posted - 2012-06-21 : 10:27:17

all the variables are VarChar but I have created the new variable employee which shows it is a numeric

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 17:43:07

see if it has precision and scale value to accomodate values you're passing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

goligol
Posting Yak Master

128 Posts

Posted - 2012-06-22 : 12:40:55

Thank youuuuuuuu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 22:34:43

One typical scenario where I've seen this happening is case where you dont explicitly specify precision scale values when casting and it assumes default values whereas actual values you're getting overflows it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources