|
What is the exact select statement containing the column? More details on what you are trying to do will be very helpful here. THe SQL below cause no issues when executed via DBISQL. create table t1 ( c1 char(15) );
insert into t1(c1) values ('2304981100F1');
select c1 from t1;
The existence of the letter 'F' in your string means it's not a numeric value, so I can see the same error you are seeing if I try to cast it to a numeric. select cast (c1 as numeric(100,50)) from t1 Are you trying convert a string representation of a number in base-16 to base-10? Reg
(20 Mar '23, 09:50)
Reg Domaratzki
Replies hidden
If this working fine for you then it must be a database option that prevents the error. The string contains an 'F' and in my setup of SQL Anywhere version 17 build 7236 it also fails. There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot convert '2304981100F1' to numeric
SQLCODE=-157, ODBC 3 State="07006"
Line 1, column 1
select cast ('2304981100F1' as numeric(100,50))
(20 Mar '23, 11:38)
Frank Vestjens
|
|
Hello, I was selecting a trigger with 2 tables and that is why this error is occurring. Even though in both tables, the columns are char (15), the error persists. Anyway, thank you for your help and I will look for a solution without having to use the letter "F" mixed with numbers. 1
You should not need to make a change to the data - IMO it would only be masking an implementation problem. Something is manipulating that value not as a character type. Perhaps a flaw in the trigger logic, a computed column, or a check constraint, to name a few possibilities that may explain this.
(20 Mar '23, 23:24)
Chris Keating
|
|
As others have asked, what exactly is your SELECT statement? Are you comparing the CHAR values to numeric values? If so, this will lead to trying to cast the CHAR to a numeric value and will fail because the "F" is not allowed for any numeric type. (See here for details on mixted-type comparisons... The following first two queries will raise this error, as the comparison value is provided as int resp. numeric - whereas the third will succeed because the comparison is done with string semantics (and returns an empy result set, as expected): select cast('2304981100F1' as char(15)) as MyValue where MyValue = 1;
select cast('2304981100F1' as char(15)) as MyValue where MyValue = 1.1;
select cast('2304981100F1' as char(15)) as MyValue where MyValue = '1';
So I suspect there is a comparison in your query that attempts to compare your CHAR(15) column with a numeric column or literal. |
Have you read this article? https://help.sap.com/docs/SAP_SQL_Anywhere/93079d4ba8e44920ae63ffb4def91f5b/81733f956ce21014970ff897ff8fbebf.html
Sounds that your number is two digits bigger than a 32-bit integer.