Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Not sure why the following is giving an error |
Thu, Jan 12 2012 9:57 AM | Permanent Link |
Adam Brett Orixa Systems | 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 |
Thu, Jan 12 2012 11:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Thu, Jan 12 2012 4:33 PM | Permanent Link |
Adam Brett Orixa Systems | 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 .... Attachments: PI-BackUP.EDBBkp |
Fri, Jan 13 2012 2:50 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Fri, Jan 13 2012 8:12 AM | Permanent Link |
Adam Brett Orixa Systems | >>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. |
Fri, Jan 13 2012 8:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
Without the external module I end up deleting so much I can't get anything to work. Roy Lambert |
Fri, Jan 13 2012 8:30 AM | Permanent Link |
Adam Brett Orixa Systems | 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
Attachments: PI-BackUP.EDBBkp |
Fri, Jan 13 2012 9:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Sat, Jan 14 2012 11:53 AM | Permanent Link |
Adam Brett Orixa Systems | 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 ... |
Sat, Jan 14 2012 12:33 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |