Can't call my stored procedure: "invalid number: not a valid number string"

[Former Member]

Hi everyone,

I have a problem with calling my stored procedure.

This is my SP

PROCEDURE "xyz"."test" (
    IN DVID NVARCHAR(33),
    IN START_DATE SECONDDATE, 
    IN END_DATE SECONDDATE,
    IN INTERP INTEGER
)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
    AS
BEGIN

    SELECT
            *,
            ROW_NUMBER() OVER(ORDER BY DATES) AS "INDEXX"
        FROM "DATA [....]"
        WHERE
            "DATES" >= :START_DATE
            AND
            "DATES" <= :END_DATE
            AND
            "ID" = :DVID
        ORDER BY "DATES";

END;

This is how I call my SP

CALL "xyz"."test"(
DVID => 'DE666',
START_DATE => '2014-01-01',
END_DATE => '2015-12-31',
INTERP => 32768
)

This is the result. Whats wrong here? Obviously there is a problem with the conversion...

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

What can I do now? Thanks!

[Former Member]

The COLUMN ID is of type String/NVARCHAR.
I can't edit my question...

[lbreddemann]

Ok, this gets funny as the discussion to this question started here: http://stackoverflow.com/questions/41426687/invalid-number-exception-invalid-number-not-a-valid-numb...

To dig into this a bit further, we need to understand how " FROM"DATA [....]" " really looks like.

I suspect that your data source implicitly triggers a type conversion to a number data type.This conversion fails, since 'DE666' is not a number.