Server 2005中的CASTing值浮动错误

Question

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

我有一个SQL Server 2005数据库，它具有这个精确的过程，我正在尝试使脚本转换一个测试数据库来匹配这个dev数据库。我的脚本有几行代码如下：

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

该过程实质上是“从另一个表中插入到表(所有字段)，其中的字段= @input”。

当我运行脚本时，我得到了错误：

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

也没有创建这个过程。但是，我已经比较了开发环境和测试环境中的源表，它们完全匹配。该过程与dev环境中的脚本完全相同。

我不能问我的同事，如果他必须做任何特殊的杂技来创造这个剧本，所以我问你。我已经做了一些搜索，并看到也许float应该是FLOAT(6,1) (或者类似的)形式，但这不是他所拥有的，而且我不喜欢改变测试环境，这样它就不会真正匹配dev。

添加了

评论是正确的。有人告诉我，错误发生在以下强制转换中：

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

我可以发布整个查询，但这是一个很长的查询！因此，相反，我将只包括铸造字段，以及第一个和最后一个字段。我想问问我的同事，这一领域是否是一个错误，它只是需要一个直接的演员。他下星期一会回来，所以可能需要等那么久。

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被定义为

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被定义为

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
)

Answer 1

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

如果我将一条冒犯行改为

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

至

CAST(TRACER_YIELD AS FLOAT),

它起作用了。

它能正常工作吗？

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

Answer 2

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

Answer 3

嗯，为什么强制转换，varchar到数值有一个隐式转换，只要数据是正确的(我猜想您正在检查您的清理过程，以确保数据是数字的，然后尝试将其插入到生产中，如果有人在字段中放置字符数据，即使转换也会失败)。