SQL0802 - Data Conversion or Data Mapping Error

问题:

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"

回答1:

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:

• 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.).

回答2:

If divide by zero is your problem, consider that there may be no point in including rows that have no sales.

Consider using a CASE expression to do division only when the sales are not zero, but use null when the sales are zero.