"invalid number exception: invalid number: not a valid number string" when call a stored procedure

Question

I have a small stored procedure:

CREATE PROCEDURE "XYZ"."TEST" (
    IN KKK_ID NVARCHAR(12),
    IN START_DATE SECONDDATE, 
    IN END_DATE SECONDDATE,
    IN INTER INTEGER
)
    SELECT
        *,
        ROW_NUMBER() OVER(ORDER BY "DATES") AS "INDEXX"
    FROM "XYZ::DATA"
    WHERE
        "DATES" >= :START_DATE
        AND
        "DATES" <= :END_DATE
        AND
        "ID" = :KKK_ID
    ORDER BY "DATES";

When I try to call this procedure, I'm getting this error message:

CALL "XYZ"."TEST"('DE666', '2014-01-01', '2015-12-31', 2234);

invalid number exception: invalid number: not a valid number string 'DE666'

But the 'D666' looks like a string - at least for me. Whats wrong here?

PS: the id is not a reserved word.

Answer 1

Ok, the error message doesn't say "'DE666' is not a valid string." What it says is "'DE666' is not a valid number string". That means that to HANA this string is not a valid string representation of a number. Valid representations are those that can be converted into a number, e.g. '1', '2.34', '4,323,252.2354' etc. That 'DE' bit in your string simply doesn't make sense to the data type conversion routine.

So, that's about the error message. The second point you made is that the base column for "ID" is actually a NVARCHAR column, so that it's not quite clear, why there is a type conversion in the first place. In my experience, this sort of conversions occurs due to implicit conversions, that have not been minded by the developer. E.g. the return type definition of "XYZ::DATA" (could be a view or a table function) might have been declared as a number. This is easy to overlook and not uncommon.

If you cannot find such a wrong type declaration, then place post all DDL to reproduce the problem.

Answer 2

I faced a similar error while running a long query.

The part where the error occurred was something like

where m.member = 'id'

So I just changed it to

where m.member like 'id%'

and voila! It worked! That error didn't show up anymore.

I still have no idea the reason behind why the error came in the first place or how this solved the problem, but hey, as long as the solution works.

Just posting this in case others like me encounter this issue in the future.