Icon View Incident Report

Serious Serious
Reported By: Eduardo Pereira
Reported On: 12/27/2007
For: Version 4.25 Build 7
# 2508 Invalid Floating Point Error With ORDER BY On SELECT Expression Containing STDDEV Function

The SQL below is returning the error: Invalid Floating Point Operation in SQL.


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



Comments Comments
The ORDER BY was messing up the STDDEV calculations.


Resolution Resolution
Fixed Problem on 12/28/2007 in version 4.26 build 1


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM ODBC Trial
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source
DBISAM VCL Trial

Image