使用CTE进行无效的浮点运算(Invalid floating point operation with CTE)

使用CTE进行无效的浮点运算(Invalid floating point operation with CTE)

我收到此代码发生了无效的浮点运算错误。 它告诉我问题是CTE开始的问题。 如果我没有SQRT功能，那么一切正常，但是一旦我添加了SQRT，我就会收到错误。

我想要做的是通过平方数来摆脱负面。

declare @start_date date, @end_date date set @start_date = '2012/01/01' set @end_date = '2012/12/31'; with tbl1 as ( select strata, entranceID, sum(count)/count(*) as AverageCount from train where surveydate between '2012/01/01' and '2012/12/31' group by strata, entranceID ) select a.jobnumber, a.strata, a.EntranceID, Count, b.AverageCount, count - AverageCount , CASE WHEN AverageCount = 0 then 0 ELSE SQRT(count - AverageCount) END as A

I'm getting An invalid floating point operation occurred error with this code. It's telling me that the problem is on line where the CTE starts. If I don't have the SQRT function, then everything work fine, but as soon as I add the SQRT I get the error.

What I'm trying to do is to get rid of the negative by squaring the number.

declare @start_date date, @end_date date set @start_date = '2012/01/01' set @end_date = '2012/12/31'; with tbl1 as ( select strata, entranceID, sum(count)/count(*) as AverageCount from train where surveydate between '2012/01/01' and '2012/12/31' group by strata, entranceID ) select a.jobnumber, a.strata, a.EntranceID, Count, b.AverageCount, count - AverageCount , CASE WHEN AverageCount = 0 then 0 ELSE SQRT(count - AverageCount) END as A

最满意答案

在表达式中：

SQRT(count - AverageCount)

count的值必须> = AverageCount。

这可能不适用于您的测试数据。

考虑使用：

SQRT (ABS(count - AverageCount))

相反，如果这在你的情况下是有道理的。

In the expression:

SQRT(count - AverageCount)

The value of count has to be >= AverageCount.

This may not be true for your test data.

Consider using :

SQRT (ABS(count - AverageCount))

Instead, If that makes sense in your case.