| Author |
 Topic  |
|
|
goligol
Posting Yak Master
120 Posts |
 Posted - 06/20/2012 : 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
United Kingdom
3560 Posts |
Posted - 06/20/2012 : 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
120 Posts |
Posted - 06/20/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 06/20/2012 : 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
120 Posts |
Posted - 06/20/2012 : 13:40:54
|
| EMP is a number. |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 06/20/2012 : 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
120 Posts |
Posted - 06/20/2012 : 16:32:15
|
| How should I creat DDL:-( |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48033 Posts |
Posted - 06/20/2012 : 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
120 Posts |
Posted - 06/21/2012 : 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
India
48033 Posts |
Posted - 06/21/2012 : 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
120 Posts |
Posted - 06/22/2012 : 12:40:55
|
| Thank youuuuuuuu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48033 Posts |
Posted - 06/22/2012 : 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/
|
|
|
| |
Topic |
|
Variable type
