Question Re: Cannot convert to a numeric SQLA16 in Technology Q&A

Cannot convert to a numeric SQLA16

former_SQLA_member1694965 — Sun, 19 Mar 2023 21:42:49 GMT

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.

Re: Cannot convert to a numeric SQLA16

fvestjens — Mon, 20 Mar 2023 03:37:33 GMT

What is the exact select statement containing the column?

Re: Cannot convert to a numeric SQLA16

Vlad — Mon, 20 Mar 2023 07:45:23 GMT

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.

Re: Cannot convert to a numeric SQLA16

regdomaratzki — Mon, 20 Mar 2023 09:50:50 GMT

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

Re: Cannot convert to a numeric SQLA16

fvestjens — Mon, 20 Mar 2023 11:38:33 GMT

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

Re: Cannot convert to a numeric SQLA16

former_SQLA_member1694965 — Mon, 20 Mar 2023 21:02:09 GMT

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.

Re: Cannot convert to a numeric SQLA16

chris_keating — Mon, 20 Mar 2023 23:24:28 GMT

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.

Re: Cannot convert to a numeric SQLA16

VolkerBarth — Tue, 21 Mar 2023 03:50:09 GMT

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.