Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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.

asked 19 Mar '23, 21:42

jorgeftz's gravatar image

jorgeftz
24336
accept rate: 0%

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.

(20 Mar '23, 07:45) Vlad

What is the exact select statement containing the column?

permanent link

answered 20 Mar '23, 03:37

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k364766
accept rate: 21%

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.

permanent link

answered 20 Mar '23, 21:02

jorgeftz's gravatar image

jorgeftz
24336
accept rate: 0%

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.

permanent link

answered 21 Mar '23, 03:50

Volker%20Barth's gravatar image

Volker Barth
40.3k363551823
accept rate: 34%

edited 21 Mar '23, 03:53

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×9

question asked: 19 Mar '23, 21:42

question was seen: 967 times

last updated: 21 Mar '23, 03:53