SQL0802 - 数据转换或数据映射错误 [英] SQL0802 - Data Conversion or Data Mapping Error

问题描述

我正在尝试编写一个在服务器端计算一些数字的查询，而不是在拉出数据之后。我不断得到SQL0802错误。我已经尝试了一个常规的 Sum 以及 Double 和 float 命令。我认为回归是漫长的。我使用SQL Squirrel，所以我去了删除小数位限制，看看是否会解决问题。这是把它抛弃的毛利率计算。其余的计算工作正常。我感谢任何帮助我可以得到。这只是代码的一部分。我省略了 Where ， Group By 和 Order By 部分为了空间：

I'm am trying to write a query that calculates some numbers on the servers side, rather than after the data is pulled. I keep getting the SQL0802 error. I have tried a regular Sum as well as Double and float commands. I think the return is to long. I'm using SQL Squirrel so I went and removed the decimal place restriction to see if that would fix the problem. It is the "Gross Margin" calculation that is throwing it off. The rest of the calculations work fine. I appreciate any help i can get. This is just a portion of the code. I omitted the Where, Group By, and Order By sections for the sake of space:

Select  Distinct DB1.Tb1.STORE,
        DB1.Tb2.DATE_ID,

Sum (DB1.Tb1.CUR_CASH_SALES+DB1.Tb1.CUR_CHARGE_SALES) As "Total Sales",

Sum (DB1.Tb1.CUR_CASH_COST+DB1.Tb1.CUR_CHARGE_COST) As "Total Cost",

Sum ((DB1.Tb1.CUR_CASH_SALES+DB1.Tb1.CUR_CHARGE_SALES)-DB1.Tb1.CUR_CASH_COST+DB1.Tb1.CUR_CHARGE_COST)) As "Gross Profit",

Sum (((DB1.Tb1.CUR_CASH_SALES+DB1.Tb1.CUR_CHARGE_SALES)-(DB1.Tb1.CUR_CASH_COST+DB1.Tb1.CUR_CHARGE_COST))/(DB1.Tb1.CUR_CASH_SALES+DB1.Tb1.CUR_CHARGE_SALES)))As "Gross Margin"

推荐答案

是除以零，你只需要防止在分母为零时发生分裂。最简单的方法是分别给分子和分母 sum ，然后再分割。

If the problem is divide-by-zero, you simply need to prevent the division from happening when the denominator is zero. The easiest way would be to sum the numerator and denominator separately, then divide after that happens:

       sum (
           DB1.Tb1.CUR_CASH_SALES+DB1.Tb1.CUR_CHARGE_SALES-
           DB1.Tb1.CUR_CASH_COST+DB1.Tb1.CUR_CHARGE_COST
       ) /
       sum (
           DB1.Tb1.CUR_CASH_SALES+DB1.Tb1.CUR_CHARGE_SALES
       ) as "Gross Margin"

然后，如果您正在分组的整个类别的分母为零，则只能得到一个除数。

Then you would only get a divide-by-zero if the denominator is zero for the entire category you are grouping on.

如果还是一个问题，你还需要做另外一件事。根据您的需要，您可以：

If it is still a problem, you will need to do something else in addition. Depending on what you want, you could:

• 使用其中子句排除在您的输出中具有零分母的组。


• 在整个计算之前放置一个 case 语句，用于在计算之前检查分母是否为零。如果为零，则用任何有意义的输出替换计算（零，零等）。

• Use your where clause to exclude groups with a zero denominator from your output.
• Put a case statement around the entire calculation that checks if the denominator is zero before calculating. If it is zero, replace the calculation with whatever output makes sense (zero, null, etc.).

这篇关于SQL0802 - 数据转换或数据映射错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！