SAP HANA CX_AMDP_EXECUTION_FAILED Dump SQL code 339 Date SQL

Question

I have a sqlscript inside an AMDP class. I get a dump on the following statement:

sel1 = select mandt, equnr,
    ROW_NUMBER() OVER ( PARTITION BY equnr ORDER BY equnr, idate, itime, mdocm) as rnum,
    to_date(idate) as idate,
    cast(to_varchar(idate) || to_varchar(itime) as "$ABAP.type( TSTMP_BW_EXTRACT )" ) AS mytimestmp,
    LEAD(cast(to_varchar(idate) || to_varchar(itime) as "$ABAP.type( TSTMP_BW_EXTRACT )" )) OVER ( ORDER 
       BY equnr, idate, itime, MDOCM) timdelta,
    to_decimal('0.0',25,6) as mydiff,
    VLCOD,
    LEAD(vlcod, 1) OVER ( ORDER BY equnr,idate,itime,MDOCM) as nxtVlcod,    
    TO_DECIMAL('0.0',25,6) as T_PRESS_RUN,    
    TO_DECIMAL('0.0',25,6) as T_PRESS_DWN,    
    TO_DECIMAL('0.0',25,6) as T_UPRESS_DWN    
    from :sel_imrg    
     where equnr = :v_equnr    
       and idate between v_date_begin and v_date_end    
     order by mandt, equnr, idate, itime, MDOCM;

The issue seems to be with converting separate idate and itime fields into a timestamp. I've tried many different data types for the timestamp value, such as TIMESTAMP and the ABAP type shown above. I've also tried to_timestamp(), cast(), to_decimal(), etc.

Strange thing is, almost exact same statement worked just fine in the HANA studio SQL console in a HANA sidecar.

The dump says CX_AMDP_EXECUTION_FAILED SQL error 339 "[339] (range 3) invalid number exception: invalid number: "TST"."SAPTST"."ZCL_TEST_CLASS=>EXECUTE#stb2#20210405110801": | | l"

System is S/4 1809 SAP_ABA 75D SP5 on HANA 2.00.048.00.1591276203

I appreciate any help you all can provide.

Answer 1

The error message "[339] (range 3) invalid number exception: invalid number refers to a failed conversion of a string to a number data type.

This means, that this is not about converting numbers or strings to a date or timestamp data type.

A potential cause for an implicit conversion is this part of the WHERE clause:

where equnr = :v_equnr  

If :v_equnr is a numeric data type but the column equnr is a character data type, an implicit type conversion needs to be performed, i.e. the string equnr gets type cast into a numeric data type. When column equnr contains characters at all, this will produce the error the OP encountered.

So, this depends not just on the statement and table structure but also on the actual data in the table, which may be the reason the same statement worked on another system.

Answer 2

SAP support helped me solve the problem.

Although SAP support didn't specifically mention it, there appears to be a bug in hana sql script (or undocumented feature, depending on how you look at it)

If you you are trying to form a timestamp value from a separate date and time value, you must concatenate date and time together with a space in between 'YYYY-MM-DD HH24:MI:SS'. However, you can't just use ' ' for space. You must use the ascii representation for space: char(32).

As I mentioned previously. The original version of this code worked just fine inside an anonymous block in HANA studio. Only when it is inside an AMDP did it dump.

This code successfully executes inside AMDP with no dumps:

sel1 = select mandt,
   EQUNR,
   ROW_NUMBER() OVER ( PARTITION BY equnr ORDER BY equnr, idate, itime, MDOCM) as rnum,
   to_date(idate) as idate,
   ifnull(to_timestamp( idate || char(32) || itime, 'YYYY-MM-DD HH24:MI:SS'), '0001-01-01 00:00:00') AS mytimestmp,
   ifnull(LEAD(to_timestamp( idate || char(32) || itime, 'YYYY-MM-DD HH24:MI:SS')) OVER ( ORDER BY equnr, idate, itime, MDOCM), '0001-01-01 00:00:00') timdelta,
   to_decimal('0.0',25,6) as mydiff,
   VLCOD,
   LEAD(vlcod, 1) OVER ( ORDER BY equnr,idate,itime,MDOCM) as nxtVlcod,
   TO_DECIMAL('0.0',25,6) as T_PRESS_RUN,
   TO_DECIMAL('0.0',25,6) as T_PRESS_DWN,
   TO_DECIMAL('0.0',25,6) as T_UPRESS_DWN
    from :sel_imrg
     where equnr = :v_equnr
       and idate between v_date_begin and v_date_end
     order by mandt, equnr, idate, itime, MDOCM;