CODEWITHSUNDEEP
sqlsybase
Scout75
Scout75

Reputation: 11

Error when a field is declared as NULL in where clause

We have a query running on sybase and we get the below error for certain account numbers but works for others

sqlanywhere error 1009145: Data type conversion not possible integer(10,0) to varchar(6,0) (oselib/hos_dfe.cxx 13811)

So i started debugging it and found out the error was coming from a field in the format of an int( values are like 20,200,721). This field is declared as NULl in the where statment like FieldA is NULL.

So when i changed to STR(FieldA) it started to work and it all good now.

But my question is why would that cause the above error. Its an integer and the statement is only checking for values which are null and the values in the db are [NULL].

Any ideas why that is happening with this field?

Upvotes: 0

Views: 82

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

This is NOT an integer: '10,000'. It is a string with digits and commas.

This is NOT an integer: '10000'. It is a string with just digits.

Sybase can only convert strings that are all digits to integers, and commas are not digits. (Okay, it can also handle leading negative signs too.) You can remove the commas using replace():

cast(replace(col, ',', '') as int)

Upvotes: 1

Related Questions