Question Re: Invalid SQL Function SERIES_GENERATE_INTEGER in AMDP Procedure after Update in Technology Q&A

Invalid SQL Function SERIES_GENERATE_INTEGER in AMDP Procedure after Update

lkempkens — Thu, 16 Mar 2023 09:59:45 GMT

Hi,

we used the SQL Function SERIES_GENERATE_INTEGER in an AMDP-Procedure.

This AMDP-Procedure worked fine on a Netweaver 7.50 on HANA 2.

After upgrading to S/4 HANA 2022 Initial Shipment, the following code doesn't compile anymore:

method fill_temporary_templates
by database procedure
for hdb language sqlscript.
 lt_ext_event_binder_tmp = 
   select generated_period_start as binder
   from series_generate_integer( 1, 20, 90 );
endmethod.

Error: SERIES_GENERATE_INTEGER is unkown.

Does anybody know why this function doesn't work anymore in an AMDP-Procedure?
The HANA-DB itself executes the command without any concern:

Thanks!

Lars

Re: Invalid SQL Function SERIES_GENERATE_INTEGER in AMDP Procedure after Update

chaouki_akir — Fri, 17 Mar 2023 19:43:19 GMT

Hello,

do you see it there : transaction ST04 select * from "SYS"."FUNCTIONS" ?

If no, you may ask SAP support for why the function is missing aftere the upgrade

Our system is SAP 7.56 SP 1

Re: Invalid SQL Function SERIES_GENERATE_INTEGER in AMDP Procedure after Update

fedaros — Fri, 17 Mar 2023 20:33:28 GMT

Hi Lars,

Maybe add schema SYS is necessary, doesn't make sense as I found a public synonym on HANA DB.

Anyhow I found ddic reading class CL_OSQL_SYSFUNCTIONS and look the method:

  method generate_integer by database function for hdb language sqlscript.
    return select * from sys.series_generate_integer( :step, :from_value, :to_value );
  endmethod.

Fired by this data definition with same name SERIES_GENERATE_INTEGER:

@EndUserText.label: 'Generate integer series'
@ClientHandling.type: #CLIENT_INDEPENDENT
@AccessControl.authorizationCheck: #NOT_ALLOWED
define table function series_generate_integer
with parameters
  step : abap.int4,
  from_value : abap.int4,
  to_value   : abap.int4
returns {
  generated_period_start : abap.int4;
  generated_period_end : abap.int4;
  element_number : abap.int8;
  fraction_of_min_max_range : abap.fltp; 
}
implemented by method cl_osql_sysfunctions=>generate_integer

Regards, Fernando Da Rós

Re: Invalid SQL Function SERIES_GENERATE_INTEGER in AMDP Procedure after Update

lkempkens — Sun, 19 Mar 2023 14:18:08 GMT

Thank you, Fernando! That was the clou. After adding the (now) missing prefix "sys.", I was able to call the function without exception.

Re: Invalid SQL Function SERIES_GENERATE_INTEGER in AMDP Procedure after Update

lkempkens — Sun, 19 Mar 2023 14:20:12 GMT

Thank you, Chaouki. I wasn't aware, that this table exists and in that it shows the missing schema (same as Fernando explainend in his post). That helped me to understand the missing link.

Re: Invalid SQL Function SERIES_GENERATE_INTEGER in AMDP Procedure after Update

Sandra_Rossi — Sun, 19 Mar 2023 15:11:51 GMT

Be careful with <br> code formatting errors in the SAP Community forum. This is better formatted:

method fill_temporary_templates
  by database procedure
  for hdb language sqlscript.
  
 lt_ext_event_binder_tmp = 
   select generated_period_start as binder
   from series_generate_integer( 1, 20, 90 );
endmethod.