Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
ERROR: Invalid Floating Point Operation in SQL |
Thu, Dec 27 2007 8:44 AM | Permanent Link |
Eduardo Pereira | The SQL below is returning this error.
[code] SELECT CONCAT(POINTNAME WITH '.X') AS POINTNAME, STDDEV(X) * 6 AS SIGMA, MIN(X) AS MINIMA, MAX(X) AS MAXIMA, AVG(X) AS MEDIA, MAX(X) - MIN(X) AS RANGE, CAST((((AVG(ZEROX) + AVG(LINFX)) - (AVG(ZEROX) - AVG(LINFX))) / (STDDEV(X) * 6)), FLOAT) AS CP, IF((((AVG(ZEROX) + AVG(LINFX)) - AVG(X)) / (STDDEV(X) * 3)) < ((AVG(X) - (AVG(ZEROX) - AVG(LINFX))) / (STDDEV(X) * 3)) THEN (((AVG(ZEROX) + AVG(LINFX)) - AVG(X)) / (STDDEV(X) * 3)) ELSE ((AVG(X) - (AVG(ZEROX) - AVG(LINFX))) / (STDDEV(X) * 3))) AS CPK FROM TBPOINTS WHERE FKID < 200 AND X <> NULL GROUP BY POINTNAME UNION SELECT CONCAT(POINTNAME WITH '.Y') AS POINTNAME, STDDEV(Y) * 6 AS SIGMA, MIN(Y) AS MINIMA, MAX(Y) AS MAXIMA, AVG(Y) AS MEDIA, MAX(Y) - MIN(Y) AS RANGE, CAST((((AVG(ZEROY) + AVG(LINFY)) - (AVG(ZEROY) - AVG(LINFY))) / (STDDEV(Y) * 6)), FLOAT) AS CP, IF((((AVG(ZEROY) + AVG(LINFY)) - AVG(Y)) / (STDDEV(Y) * 3)) < ((AVG(Y) - (AVG(ZEROY) - AVG(LINFY))) / (STDDEV(Y) * 3)) THEN (((AVG(ZEROY) + AVG(LINFY)) - AVG(Y)) / (STDDEV(Y) * 3)) ELSE ((AVG(Y) - (AVG(ZEROY) - AVG(LINFY))) / (STDDEV(Y) * 3))) AS CPK FROM TBPOINTS WHERE FKID < 200 AND Y <> NULL GROUP BY POINTNAME UNION SELECT CONCAT(POINTNAME WITH '.Z') AS POINTNAME, STDDEV(Z) * 6 AS SIGMA, MIN(Z) AS MINIMA, MAX(Z) AS MAXIMA, AVG(Z) AS MEDIA, MAX(Z) - MIN(Z) AS RANGE, CAST((((AVG(ZEROZ) + AVG(LINFZ)) - (AVG(ZEROZ) - AVG(LINFZ))) / (STDDEV(Z) * 6)), FLOAT) AS CP, IF((((AVG(ZEROZ) + AVG(LINFZ)) - AVG(Z)) / (STDDEV(Z) * 3)) < ((AVG(Z) - (AVG(ZEROZ) - AVG(LINFZ))) / (STDDEV(Z) * 3)) THEN (((AVG(ZEROZ) + AVG(LINFZ)) - AVG(Z)) / (STDDEV(Z) * 3)) ELSE ((AVG(Z) - (AVG(ZEROZ) - AVG(LINFZ))) / (STDDEV(Z) * 3))) AS CPK FROM TBPOINTS WHERE FKID < 200 AND Z <> NULL GROUP BY POINTNAME ORDER BY CP [/code] Please, help-me. It's urgent!! Attachments: TBPoints.rar |
Thu, Dec 27 2007 9:07 AM | Permanent Link |
Eduardo Pereira | More informations:
- When i put DESC on the ORDER BY clause, the SQL runs normally. - If i change the field CP on the ORDER BY clause (SIGMA, CPK), the SQL returns the same error. - Any FLOAT field on the ORDER BY clause returns this error). I need to run this SQL without the DESC clause. |
Thu, Dec 27 2007 2:54 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Eduardo,
Something related to the ORDER BY is screwing up the STDDEV() calculation, but I can't pinpoint it yet. As a workaround, you'll have to remove the ORDER BY for now until I can get a fix in place. I intend to do a new DBISAM build this upcoming week. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |