- SAP Managed Tags
Hello All,
I have a requirement where a column of table needs to be populated with Row Number based on some grouping of columns and I was trying to leverage the Open SQL's window function. The window function works fine when using in Select but when almost same select is used as Subquery in Insert, it creates dump when trying to Activate or even on Syntax check. It does not provides any Activation or Syntax Check error.
The system used is S4HANA ON PREMISE 1909 & SAP_ABA 75E
For easier replication of scenario, I copied the Standard Demo Program DEMO_SELECT_OVER and added my scenario relevant codes:
Excerpts from ST22 dumpCategory Internal error
Runtime Errors SYSTEM_CORE_DUMPED
Date and Time 03/26/2021 10:01:02 (EST)
Short Text
Process terminated by signal 11.
Requesting help or insight on how to fix this ?
Added code below for easier replication:
REPORT yyaj_demo_select_over.
CLASS demo DEFINITION.
PUBLIC SECTION.
CLASS-METHODS:
main,
class_constructor.
ENDCLASS.
CLASS demo IMPLEMENTATION.
METHOD main.
DATA(out) = cl_demo_output=>new( ).
SELECT char1 && '_' && char2 AS group,
num1,
COUNT(*) OVER( PARTITION BY char1, char2 ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1, char2 ) AS rnum,
MIN( num1 ) OVER( PARTITION BY char1, char2 ) AS min,
MAX( num1 ) OVER( PARTITION BY char1, char2 ) AS max,
SUM( num1 ) OVER( PARTITION BY char1, char2 ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1, char2 ),
2 ) AS perc
FROM demo_expressions
ORDER BY group
INTO TABLE @DATA(windowed).
DATA : ls_exp2 TYPE yytmp_demo_exp2.
INSERT VALUE ls_exp2( id = '1' num1 = 1 num2 = 1 numlong1 = 1 numlong2 = 1 ) INTO TABLE yytmp_demo_exp2.
INSERT VALUE ls_exp2( id = '2' num1 = 2 num2 = 2 numlong1 = 1 numlong2 = 1 ) INTO TABLE yytmp_demo_exp2.
INSERT VALUE ls_exp2( id = '3' num1 = 3 num2 = 2 numlong1 = 3 numlong2 = 1 ) INTO TABLE yytmp_demo_exp2.
" Created yytmp_demo_exp table with first 5 fields from Table demo_expressions
DELETE FROM yytmp_demo_exp.
" Insert with Subquery with Window Function causing Termination of WorkProcess and Dump
INSERT yytmp_demo_exp FROM (
SELECT id, 1 AS num1 , 2 AS num2,
COUNT( * ) OVER( PARTITION BY char1, char2 ) AS numlong1,
ROW_NUMBER( ) OVER( PARTITION BY char1, char2 ) AS numlong2
FROM demo_expressions
* INTO TABLE @DATA(windowed2).
).
out->display( windowed ).
ENDMETHOD.
METHOD class_constructor.
TYPES tab_type TYPE STANDARD TABLE OF
demo_expressions WITH EMPTY KEY.
DELETE FROM demo_expressions.
INSERT demo_expressions FROM TABLE @( REDUCE tab_type(
LET r1 = cl_abap_random_int=>create(
seed = CONV i( sy-uzeit ) min = 1 max = 10 )
r2 = cl_abap_random_int=>create(
seed = CONV i( r1->get_next( ) ) min = 0 max = 1 )
c = `AB` IN
INIT t TYPE tab_type
FOR i = 0 THEN i + 1 UNTIL i >= 25
NEXT t = VALUE #(
BASE t ( id = sy-abcde+i(1)
num1 = r1->get_next( )
char1 = substring( val = c
off = r2->get_next( )
len = 1 ) &&
substring( val = c
off = r2->get_next( )
len = 1 )
char2 = substring( val = c
off = r2->get_next( )
len = 1 ) &&
substring( val = c
off = r2->get_next( )
len = 1 ) ) ) ) ).
ENDMETHOD.
ENDCLASS.
START-OF-SELECTION.
demo=>main( ).Table:
@EndUserText.label : 'Database Table for Expression Examples'
@AbapCatalog.enhancementCategory : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #ALLOWED
define table yytmp_demo_exp {
key mandt : mandt not null;
key id : char1 not null;
num1 : abap.int4;
num2 : abap.int4;
numlong1 : abap.int8;
numlong2 : abap.int8;
}
Even before the dump occurs, a pop up occurs saying > Work process restarted; ABAP session terminated (location ************)
Thanks
Bluesky