3

I have a table with a string column. I convert this column to a number using the function TO_INTEGER(). It works fine. But if I aggregate the converted column with the function SUM I got this error:

SAP DBTech JDBC: [339]: invalid number: not a valid number string ' ' at function to_int()

This is my sample SQL query:

select SUM(PARTICIPANT)
from (
select TO_INTEGER(STUDENT) as PARTICIPANT
from MyTable)

Column STUDENT is a varchar(50) in MyTable

What did I do wrong?

Thanks in advance

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
ayahya82
  • 374
  • 3
  • 6
  • 18

2 Answers2

1

Without seeing your Column values, it looks like you're trying to convert the numeric sequence at the end of your values list to a number, and the spaces that delimit it are throwing this error. But based on the information you've given us, it could be happening on any field.

Eg:

Create table Table1 (tel_number number);
Insert into Table1 Values ('0419 853 694');

The above gives you a

invalid number

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • 1
    Thank you Kumar for your replay. Yes some value of the column Student ist like '10 2' whatsoever. I guess I have to replace all spaces in the column using REPLACE_REGEXPR and then use the function TO_INTEGER or SUM. Or do you have better idea? Thanks – ayahya82 Jun 21 '16 at 13:28
0

Kindly Check Filter/ where clause if you try to give string column value as integer that time you got this error message. I wrote in HANA - Calculation view - SQL Script, In where clause Bukrs (Company Code) = 1000 after that i changed Bukrs = '1000'. then this issue is resolved.

Arivarasan Perumal
  • 1