SAP HANA

 

DROP PROCEDURE ""."ZCONCAT_EKKO_EBN";
CREATE PROCEDURE ""."ZCONCAT_EKKO_EBN"(OUT RESULT ""."OUT_SPLIT_EK_EBN") 
LANGUAGE SQLSCRIPT AS
BEGIN
    DECLARE _ZHETONG NVARCHAR(1000) ARRAY;
    DECLARE _ZEBELN  NVARCHAR(5000) ARRAY;
    DECLARE _ZHT NVARCHAR(1000); 
    
    DECLARE _INDEX INTEGER := 1;
    DECLARE _IND1 INTEGER := 1;
    
    DECLARE CURSOR C_CURSOR1 FOR                
      SELECT DISTINCT ZHETONG,EBELN FROM "CTNT_ECC"."EKKO" WHERE BUKRS LIKE '21%' AND ZHETONG NOT IN ('','0','1','TS','无')
      ORDER BY ZHETONG,EBELN;           
    FOR CUR_ROW1 AS C_CURSOR1 DO 
    
      IF _IND1 = 1 THEN
        _ZHT := CUR_ROW1.ZHETONG;
        _ZHETONG[:_INDEX] := '';
        _ZEBELN[:_INDEX] := '';
      END IF; 
     
      IF CUR_ROW1.ZHETONG = :_ZHT THEN
       _ZHETONG[:_INDEX] := CUR_ROW1.ZHETONG;
       _ZEBELN[:_INDEX] := :_ZEBELN[:_INDEX]||CUR_ROW1.EBELN||',';
      ELSE
      _INDEX := :_INDEX + 1; 
      _ZHETONG[:_INDEX]  := CUR_ROW1.ZHETONG;
      _ZEBELN[:_INDEX] := CUR_ROW1.EBELN ||',';
      _ZHT := CUR_ROW1.ZHETONG;
      END IF;
      
       _IND1 := :_IND1 + 1;
    END FOR;  
        
      RST = UNNEST(:_ZHETONG, :_ZEBELN) AS ("ZHETONG", "EBELN");
      
      RESULT = SELECT ZHETONG,
                      LEFT(EBELN,LENGTH(EBELN)-1) AS EBELN  
                 FROM :RST;
          
END;

CALL "ZB_CHENWH"."ZCONCAT_EKKO_EBN"(?) ;