Invalid character/Bad Character removal in BW4HANA using AMDP Class

Introduction

In our BW system on oracle database, we had used function module from the blog https://blogs.sap.com/2017/06/28/removing-invalid-characters-from-bw-load/ to remove the bad characters in the incoming data.

Later we had migrated to BW4HANA system and hence we had to replace the existing bad character/invalid character Function module in BW4HANA. The reason is to replace was because we wanted make the transformations pure “SAP HANA Runtime” and don’t want to rely on the ABAP function module. This blog shows the AMDP class which was built to replace the function module.

Applies to

BW/4HANA

Summary

In BW4HANA system we had used the AMDP class and method to replace the existing bad character removal Function Module and use the AMDP method in the transformations within BW4HANA.

Author          : Lakshminarasimhan Narasimhamurthy

Created on   : 26/Sep/2021

Body

Requirement

The AMDP class and static method is given below. The class and method will be used in the transformation to remove the bad characters.

class zcl_invalid_cha_rem_01 definition
  public
  final
  create public .
************************************************************************
*  Author's Name        : Lakshminarasimhan Narasimhamurthy            *
*  Create Date          : 24/Sep/2021                                  *
*  Last Update          : 25/Sep/2021                                  *
*  Description          : AMDP Class to remove bad characters          *
*----------------------------------------------------------------------*
* Reference of the blog https://blogs.sap.com/2017/06/28/removing-invalid-characters-from-bw-load/to 
* remove the bad characters.
* The prerequisite is to maintain "ALL_CAPITAL_PLUS_HEX" in RSKC setting.
* The function module has been replaced with AMDP Class to support the HANA runtime.
public section.
interfaces if_amdp_marker_hdb.
CLASS-methods INVALID_CHAR_REMOVAL importing VALUE(IV_IOBJ_NAME) TYPE RSCHABASNM
                                             VALUE(IV_text) TYPE string
                                             value(IV_lowr_case) type CHAR1
                                   exporting VALUE(RESULT) TYPE string.
protected section.
private section.

endclass.



class zcl_invalid_cha_rem_01 implementation.

method INVALID_CHAR_REMOVAL by database procedure
                            for hdb
                            language sqlscript.
--                          USING rsdcha rsdchabas.
declare lv_flag varchar( 1 );
declare lv_length integer := 0;
declare lv_counter integer := 1;
RESULT = :iv_text;
-- If the incoming string is not initial
IF :RESULT != '' then
    -- To get the length of the string
    SELECT LENGTH( :result ) into lv_length FROM "SYS"."DUMMY";
    -- Remove leading and trailing blank spaces
    select trim( both '' from :result ) into result from "SYS"."DUMMY";
    -- if incoming string is greater than 1
    IF ( SELECT LENGTH( :result ) FROM "SYS"."DUMMY" ) > 1 THEN
    -- check if the first char of the string is !, then take the string
    -- from the first char != ! to end of the string
        if SUBSTRING (:result, 1 , 1) = '!' then
            while :lv_counter <= :lv_length do
                if SUBSTRING(:result,:lv_counter,1) != '!' then
                  break;
                end if ;
                lv_counter = :lv_counter + 1;
            end while ;
            if :lv_counter = :lv_length then
                result = '';
            else
                select SUBSTRING(:result,:lv_counter,:lv_length) into result from "SYS"."DUMMY";
            end if;
        end if ;
    -- Eliminate the # sign completely
        select REPLACE_REGEXPR('([^[:print:]|^[\x{00C0}-\x{017F}]|[#])' IN :result with ' ' occurrence all) into result from "SYS"."DUMMY";

        if :iv_iobj_name != '' then
--          Remove the select statement from AMDP
--          instead pass it when calling the AMDP Procedure
--          SELECT b.lowercase INTO lv_flag FROM rsdcha AS a INNER JOIN rsdchabas AS b
--          ON a.chabasnm = b.chabasnm
--          WHERE a.chanm = :IV_IOBJ AND
--          a.objvers  = 'A' AND
--          b.objvers  = 'A';

--          if lowercase flag is not set then convert to upper case
            if :iv_lowr_case = '' then
                select upper( :RESULT ) into RESULT from "SYS"."DUMMY" ;
            end if;
        end if;
    -- if incoming string is equal to 1
    else
        select replace( :RESULT, '#', '') into result from "SYS"."DUMMY";
        if SUBSTRING (:result, 1 , 1) = '!' then
            RESULT = '';
        end if ;
    END IF;
END IF;

-- Trim to remove leading and trailing blank spaces before returning the result
select trim( both '' from :result ) into result from "SYS"."DUMMY";

endmethod.
endclass.

The static method above can be used in the transformation of BW system.

We need to pass the below 3 parameters

•  IV_IOBJ_NAME       = InfoObject name Ex:’0TXTLG’
•  IV_TEXT                  = Input string
•  IV_LOWR_CASE     = If the InfoObject allows lowercase then pass ‘X’

I have used the below code in the transformation routine and the same code can be used inside the end routine too.

• we need to identity the number of incoming record
• loop the records
• For every record call the method with Infoobject, text and lower case indicator ZCLXBW4AMDP_INVALID_CHA_REM_01=>INVALID_CHAR_REMOVAL

Earlier the lower case selection was inside the function module, now in the AMDP method i have removed it because we had to call the AMDP method multiple time and hence multiple hits to DB must be avoided. instead i have placed it as a parameter which is passed to the AMDP method.

So even though the number of incoming records is 100,000 the select statement will be called only once per InfoObject.

METHOD S0001_G01_R10 BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY
USING ZCL_INVALID_CHA_REM_01=>INVALID_CHAR_REMOVAL.
*-- target field: 0TXTLG
*-- *** Begin of routine - insert your code only below this line ***

-- Note the _M class are not considered for DTP execution.
-- AMDP Breakpoints must be set in the _A class instead.
-- Code for Invalid char removal Starts
declare lv_text STRING;
-- Flag to check lowercase
declare lv_flag varchar( 1 );
declare lv_i integer;
declare counter integer;
declare lv_length integer;

-- Exit handler in case of exceptions
declare exit handler for sqlexception
SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE, LV_TEXT, lv_i FROM "SYS"."DUMMY";

--count the number of records in table
select count( * ) into counter from :inTab;
lt_intab = select * from :inTab;

--check lower case is enabled for InfoObject
SELECT b.lowercase INTO lv_flag FROM rsdcha AS a INNER JOIN rsdchabas AS b
ON a.chabasnm = b.chabasnm
WHERE a.chanm = '0TXTLG' AND
a.objvers  = 'A' AND
b.objvers  = 'A';

-- For every incoming record
for lv_i in 1..:counter do
    lv_text = '';
    lv_length = 0;
-- get the text 
    lv_text = :lt_intab.TXTLG[ :lv_i ];
    select LENGTH (:lv_text) into lv_length from "SYS"."DUMMY";
    call "ZCL_INVALID_CHA_REM_01=>INVALID_CHAR_REMOVAL"( IV_IOBJ_NAME => '0TXTLG', 
                                                         IV_text => :lv_text, 
                                                         IV_lowr_case => 'X', 
                                                         RESULT => LV_TEXT) ;
-- from first character to length of the infoobject
    select substr( :LV_TEXT, 1, 60 ) into LV_TEXT from "SYS"."DUMMY";
    lt_intab.TF_BUDNM0[ :lv_i ] = :LV_TEXT;
end for;

outTab = SELECT TXTLG, RECORD, SQL__PROCEDURE__SOURCE__RECORD  FROM :lt_intab;

-- *** End of routine - insert your code only before this line ***
ENDMETHOD.