Cannot convert to a numeric SQLA16

[former_SQLA_member1694965]

Hello,

I have a column char(15), with the data 2304981100F1

if I call a select, that show below error.. how can I fix it ?

[Sybase][ODBC Driver][SQL Anywhere]Cannot convert '2304981100F1' to a numeric

tks.

[Vlad]

Have you read this article? https://help.sap.com/docs/SAP_SQL_Anywhere/93079d4ba8e44920ae63ffb4def91f5b/81733f956ce21014970ff897...

Sounds that your number is two digits bigger than a 32-bit integer.

[fvestjens]

What is the exact select statement containing the column?

[regdomaratzki]

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

[fvestjens]

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))

[VolkerBarth]

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.

[former_SQLA_member1694965]

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.

[chris_keating]

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.