https://community.sap.com/t5/application-development-and-automation-discussions/warning-to-developers-sql-anywhere-and-numeric-datawindow-bind-variables/m-p/439009#M12878 <P>I have been working with SAP SQL Anywhere support group to pin down why a simple SQL statement was not using the primary key index and is doing a full table scan. The primary key of the table is numeric(20,0). The datawindow sql statement is of the form select * from table where key=:an_key - where :an_key is declared as a numeric argument. If I create the same datawindow but the value passed in is a string then the index is used. After much going back and forth we have determined that the issue is that PowerBuilder passes in a numeric argument as a double. A double is considered by SQL Anywhere to be an approximate datatype. If I have a primary key that is numeric(20,0) then it will not use the primary index and will do a table scan whereas if the primary key is an integer then it will use the primary key! This seems to affect all versions of Sybase SQL Anywhere. I have reproduced this in PB6.5 and PB12.5</P> Thu, 18 May 2017 16:40:37 GMT Former Member 2017-05-18T16:40:37Z https://community.sap.com/t5/application-development-and-automation-discussions/warning-to-developers-sql-anywhere-and-numeric-datawindow-bind-variables/m-p/439009#M12878 <P>I have been working with SAP SQL Anywhere support group to pin down why a simple SQL statement was not using the primary key index and is doing a full table scan. The primary key of the table is numeric(20,0). The datawindow sql statement is of the form select * from table where key=:an_key - where :an_key is declared as a numeric argument. If I create the same datawindow but the value passed in is a string then the index is used. After much going back and forth we have determined that the issue is that PowerBuilder passes in a numeric argument as a double. A double is considered by SQL Anywhere to be an approximate datatype. If I have a primary key that is numeric(20,0) then it will not use the primary index and will do a table scan whereas if the primary key is an integer then it will use the primary key! This seems to affect all versions of Sybase SQL Anywhere. I have reproduced this in PB6.5 and PB12.5</P> Thu, 18 May 2017 16:40:37 GMT https://community.sap.com/t5/application-development-and-automation-discussions/warning-to-developers-sql-anywhere-and-numeric-datawindow-bind-variables/m-p/439009#M12878 Former Member 2017-05-18T16:40:37Z