The following fairly straightforward SQL is giving me an error:

SELECT
ProductsID as ID,
CAST(SUM(QtyUsed) as DECIMAL(19,2)) as SumUsed,
SUM(TotalCost) as SumCost

FROM ProductInputs PI
GROUP BY ID

The error is "Invalid floating point operation"

If I remove the "CAST" the SQL runs.

The field QtyUsed is a FLOAT with a default value of 0.

This is a table which has been upgraded from DBISAM running back to DBISAM 2 & contains about 10k rows.

--

* Why is the error likely to be happening?
* Can I repair or otherwise clean up the table to stop this happening?
* I would like to make sure that it doesn't happen again ...

Adam

Adam

My first thought was NULLs, so I tested here and they're fine. My next was the data going out of scope (still a possibility). My third thought is its something to do with CASTing the result of an aggregate function.

My fourth thought is you shouldn't do it that way anyway

Looking at the terms involved I assume currency. Unless you're handling the inaccuracy of floats somehow a SUM could well give you a slightly erroneous result.

Try moving the CAST inside the SUM like this:

SELECT
ProductsID as ID,
SUM(CAST(QtyUsed) as DECIMAL(19,2)) as SumUsed,
SUM(TotalCost) as SumCost

FROM ProductInputs PI
GROUP BY ID

Roy Lambert [Team Elevate]

Roy

Thanks for the loyal assistance. Unfortunately your version gives the same error. I find it strange, as the the field in the table is "just" a float, so even if the records are unusual numbers and the sums are fairly complicated there shouldn't be an error.

The table is for ingredients in food products, a record is created by calculation, so a row might be "11.5% of 32.5 grams" ... so you get numbers with quite a few decimal places in some cases, which is important for the accuracy of large batches of production, but not in other situations.

I have glanced through the rows & can't see any garbage in the table.

I have repaired the table.

I have used SQL in this form countless times without errors, so I am a little mystified.

I add the table as an attachment here ....

Adam


Its barfing on something called UIDModule. I'll zap any modules / triggers later on and see what I can see. However, you may want to email Tim with it to get some real action because all I'm likely to be able to do is say "it does/doesn't give the same problem here"


Roy Lambert [Team Elevate]

>>Its barfing on something called UIDModule. I

D'Oh. Sorry Roy, all my tables use a "UID" as a sort of autoinc. I'll restructure it to change it back to an autoinc & send it direct to Tim. Thanks again.

Adam


Without the external module I end up deleting so much I can't get anything to work.

Roy Lambert

I've slimmed down the database to a single table & removed references to external modules & saved it as a backup file here in case you are curious

Adam


Now I can test

It looks as though my first guess about it being data related could be right. I tried

SELECT
ProductsID as ID,
CAST(SUM(QtyUsed) as varchar) as SumUsed
FROM ProductInputs PI
GROUP BY ProductsID

everything hunky dory. Then I tried

SELECT
ProductsID as ID,
CAST(CAST(SUM(QtyUsed) as varchar) AS DECIMAL(19,2)) as SumUsed
FROM ProductInputs PI
GROUP BY ProductsID

and get

ElevateDB Error #1011 An error occurred with the value 3.333E-5 (A conversion error occurred)

cast to integer instead of decimal and you get

ElevateDB Error #1011 An error occurred with the value 30.100784 (A conversion error occurred)

casting to float works fine. I also tried

SELECT
ProductsID as ID,
CAST(SUM(QtyUsed) as DECIMAL(19,2)) as SumUsed,
SUM(TotalCost) as SumCost
FROM ProductInputs PI
where ProductsID in (10,94)
GROUP BY ID

and that's fine so it pretty much has to be the data.

Its productid 2465 - found it by a binary search type approach. There are values like 4.99799580528925E52


You also have some strange productid's - 2054846978 & 2054846986

Roy Lambert [Team Elevate]

Wow Thanks Roy. I will review the data, particularly looking at the rows you mentioned.

Quite a few display with the "E" added in the Qty field (i.e. the very small numbers) it is impossible to tell from just looking through them whether the data is "good" or not.

>>found it by a binary search type approach

Hum, what is this & how do you do it? ... I would like to be able to use similar techniques myself.

--

>>you've got some strange productsIDs i.e. 2054846978 ...

Yup, there was a short period when the database was DBISAM & the autoinc field got slightly messed up ... pushing out massive Autoinc numbers. Worked fine though! I haven't yet got round to cleaning these up, but now you've reminded me ...

Adam

>Quite a few display with the "E" added in the Qty field (i.e. the very small numbers) it is impossible to tell from just looking through them whether the data is "good" or not.

The difficulty is with the amount of rows, not recognising the problem. A very small number (eg 5.95E-5) has a negative after E a very big number hasn't. The one I quoted works out at 5138607174829735937 (I think). I do hope product 2465 is something nice and cheap.

>>>found it by a binary search type approach
>
>Hum, what is this & how do you do it? ... I would like to be able to use similar techniques myself.

Probably the wrong terminology but

1. Do a SELECT DISTINCT ProductID FROM ProductInputs ORDER BY ProductID
2. Pick a ProductID roughly halfway through the list and try the SQL
3. Keep going roughly halfway along the none working part until you reach a point where you're trying individual ProductIDs

One of the oldest efficient search mechanisms I know. Done manually in thins case but trying to find it when there are 1200+ product ids would take an age doing it one by one.

Roy Lambert [Team Elevate]