在SQL Server 2005中将值CASTing到FLOAT时出错

I'm trying to get a stored procedure to work for a co-worker who is out sick (and thus can't be asked for guidance).

我正在尝试让一个存储过程为一个生病的同事工作(因此不能被要求提供指导)。

I have a SQL Server 2005 database that has this exact procedure, and I'm trying to make the scripts to convert a test database to match this dev database. My script has several lines like:

我有一个SQL Server 2005数据库具有这个确切的过程,我正在尝试使脚本转换测试数据库以匹配此开发数据库。我的脚本有几行,如:

CAST(RELATIVE_ERROR_RATIO AS FLOAT),
CAST(REPORTING_LIMIT AS FLOAT),

The procedure is essentially doing an "insert into table (all the fields) from another table where field = @input"

该过程基本上是“从另一个表中插入表(所有字段),其中field = @input”

When I run the script, I get the error:

当我运行脚本时,我收到错误:

CAST or CONVERT: invalid attributes specified for type 'float'

and the procedure is not created. But, I've compared the source tables in both the dev and test environments, and they match exactly. And the procedure exists exactly as scripted in the dev environment.

并且不会创建该过程。但是,我比较了开发和测试环境中的源表,它们完全匹配。并且该过程与开发环境中的脚本完全相同。

I can't ask my co-worker if he had to do any special acrobatics to create this script, so I'm asking you. I've done some searching, and see that perhaps float should be of the form FLOAT(6,1) (or some such), but that's NOT what he has, and I'm not comfortable changing the test environment so that it won't really match dev.

我不能问我的同事他是否必须做任何特殊的杂技来制作这个剧本,所以我问你。我已经做了一些搜索,并且看到浮点数应该是FLOAT(6,1)(或者其他类似的)形式,但这不是他所拥有的,我不习惯改变测试环境以便它赢了真的很适合开发。

Added

The commenter is correct. I've been told that the error is with the following cast:

评论者是对的。我被告知错误是以下演员:

CAST(TRACER_YIELD AS FLOAT(10,3)),

I could post the entire query, but it's a long one! So, instead, I'll just include the casted fields, and the first and last field. I'd like to ask my co-worker if that one field was a mistake, and it just needed a straight cast. He'll be back next monday, so it may need to wait that long.

我可以发布整个查询,但这是一个很长的问题!所以,相反,我将只包括已转换的字段,以及第一个和最后一个字段。我想问我的同事,如果一个字段是错误的,它只需要一个直接的演员。他将在下周一回来,所以可能需要等待那么久。

CREATE PROCEDURE [dbo].[our_LOAD_INPUT]
    @ourNUMBER INT
AS

INSERT INTO our_FILE (our_NUMBER,
DILUTION_FACTOR,
DISTILLATION_VOLUME,
MAXIMUM_CONTROL_LIMIT,
MDA,
MINIMUM_CONTROL_LIMIT,
NUMBER_OF_TICS_FOUND,
PERCENT_MOISTURE,
PERCENT_RECOVERY,
PERCENT_SOLIDS,
RELATIVE_ERROR_RATIO,
REPORTING_LIMIT,
REQUIRED_DETECTION_LIMIT,
RER_MAX,
RESULT,
RETENTION_TIME,
RPD,
RPD_MAXIMUM,
SAMPLE_ALIQUOT_SIZE,
SPIKE_CONCENTRATION,
TOTAL_PROPAGATED_UNCERTAINTY,
TRACER_YIELD,
TWO_SIGMA_COUNTING_ERROR,
VERSION)
SELECT FILE_NUMBER,
CAST(DILUTION_FACTOR AS FLOAT),
CAST(DISTILLATION_VOLUME AS FLOAT),
CAST(MAXIMUM_CONTROL_LIMIT AS FLOAT),
CAST(MDA AS FLOAT),
CAST(MINIMUM_CONTROL_LIMIT AS FLOAT),
CAST(NUMBER_OF_TICS_FOUND AS FLOAT),
CAST(PERCENT_MOISTURE AS FLOAT),
CAST(PERCENT_RECOVERY AS FLOAT),
CAST(PERCENT_SOLIDS AS FLOAT),
CAST(RELATIVE_ERROR_RATIO AS FLOAT),
CAST(REPORTING_LIMIT AS FLOAT),
CAST(REQUIRED_DETECTION_LIMIT AS FLOAT),
CAST(RER_MAX AS FLOAT),
CAST(RESULT AS FLOAT),
CAST(RETENTION_TIME AS FLOAT),
CAST(RPD AS FLOAT),
CAST(RPD_MAXIMUM AS FLOAT),
CAST(SAMPLE_ALIQUOT_SIZE AS FLOAT),
CAST(SPIKE_CONCENTRATION AS FLOAT),
CAST(TOTAL_PROPAGATED_UNCERTAINTY AS FLOAT),
CAST(TRACER_YIELD AS FLOAT(10,3)),
CAST(TWO_SIGMA_COUNTING_ERROR AS FLOAT),
VERSION
FROM   our_FILE_CHAR 
WHERE  our_NUMBER = @ourNUMBER

GO

our_File_CHAR is defined as

our_File_CHAR定义为

CREATE TABLE [dbo].[our_FILE_CHAR]
(
[our_NUMBER] [int] NOT NULL,
[DILUTION_FACTOR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DISTILLATION_VOLUME] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MAXIMUM_CONTROL_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MDA] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MINIMUM_CONTROL_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NUMBER_OF_TICS_FOUND] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PERCENT_MOISTURE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PERCENT_RECOVERY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PERCENT_SOLIDS] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RELATIVE_ERROR_RATIO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REPORTING_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REQUIRED_DETECTION_LIMIT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RER_MAX] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RESULT] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RETENTION_TIME] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RPD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RPD_MAXIMUM] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAMPLE_ALIQUOT_SIZE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SPIKE_CONCENTRATION] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TOTAL_PROPAGATED_UNCERTAINTY] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TRACER_YIELD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TWO_SIGMA_COUNTING_ERROR] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VERSION] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

our_File is defined as

our_File定义为

CREATE TABLE [dbo].[our_FILE]
(
[our_NUMBER] [int] NOT NULL,
[DILUTION_FACTOR] [numeric] (10, 3) NULL,
[DISTILLATION_VOLUME] [numeric] (5, 1) NULL,
[MAXIMUM_CONTROL_LIMIT] [numeric] (10, 3) NULL,
[MDA] [numeric] (10, 3) NULL,
[MINIMUM_CONTROL_LIMIT] [numeric] (10, 3) NULL,
[NUMBER_OF_TICS_FOUND] [numeric] (2, 0) NULL,
[PERCENT_MOISTURE] [numeric] (5, 1) NULL,
[PERCENT_RECOVERY] [numeric] (10, 3) NULL,
[PERCENT_SOLIDS] [numeric] (5, 1) NULL,
[RELATIVE_ERROR_RATIO] [numeric] (10, 3) NULL,
[REPORTING_LIMIT] [numeric] (10, 2) NULL,
[REQUIRED_DETECTION_LIMIT] [numeric] (10, 2) NULL,
[RER_MAX] [numeric] (10, 3) NULL,
[RESULT] [numeric] (13, 3) NULL,
[RETENTION_TIME] [numeric] (6, 2) NULL,
[RPD] [numeric] (10, 3) NULL,
[RPD_MAXIMUM] [numeric] (10, 3) NULL,
[SAMPLE_ALIQUOT_SIZE] [numeric] (10, 3) NULL,
[SPIKE_CONCENTRATION] [numeric] (10, 3) NULL,
[TOTAL_PROPAGATED_UNCERTAINTY] [numeric] (13, 3) NULL,
[TRACER_YIELD] [numeric] (10, 3) NULL,
[TWO_SIGMA_COUNTING_ERROR] [numeric] (10, 3) NULL,
[VERSION] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

3 个解决方案

#1

I believe that what you are looking for is decimal(10,3). Float has a specific defined size and precision. Decimal allows you to specify the precision, and it appears that you are looking to use a decimal, not a float.

我相信你要找的是十进制(10,3)。 Float具有特定的大小和精度。 Decimal允许您指定精度,看起来您要使用小数而不是浮点数。

#2

Hmmm, why cast at all, varchar to numeric has an implicit conversion as long as the data is correct (I presume you are checking in your cleanup process to make sure the data is numeric before trying to insert it to production, even the cast would fail if someone put character data in the field).

嗯,为什么要进行转换,varchar到numeric有一个隐式转换,只要数据是正确的(我假设您正在检查清理过程以确保数据在尝试将其插入生产之前是数字,甚至转换为如果有人在字段中放置字符数据,则会失败)。

#3

(I can't accept my own answer, but this was the solution that worked for me.)

(我不能接受我自己的答案,但这是对我有用的解决方案。)

If I change the one offending line from

如果我改变了一条违规行

CAST(TRACER_YIELD AS FLOAT(10,3)),

CAST(TRACER_YIELD AS FLOAT),

it works.

Will it work properly?

它会正常工作吗?

Update: It was an error by the original programmer. So the change to not specify the decimal places (as shown above) is what he intended, and should work properly.

更新:这是原始程序员的错误。因此,不指定小数位(如上所示)的更改是他的意图,并且应该正常工作。

#1