Using REPLACE_REGEXPR in BW transformation throws syntax error

Question

I'm trying to implement a routine for replacing some invalid characters in a BW transformation. But I keep getting a syntax error. This is my current code:

METHOD S0001_G01_R40 BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT 
OPTIONS READ-ONLY.
-- target field: 0POSTXT
-- Note the _M class are not considered for DTP execution.
-- AMDP Breakpoints must be set in the _A class instead.

outTab = SELECT REPLACE_REGEXPR('([^[:print:]|^[\x{00C0}-\x{017F}]|[#])' 
IN "SGTXT" WITH '' OCCURRENCE ALL ) AS "/BI0/OIPOSTXT"
FROM :inTab;

errorTab = SELECT '' AS ERROR_TEXT,
          '' AS SQL__PROCEDURE__SOURCE__RECORD FROM DUMMY
          WHERE DUMMY <> 'X';
ENDMETHOD.

I keep getting the following error:

 SQLSCRIPT message: return type mismatch: Procedure 
 /BIC/QCW72C4IJDC8JAFRICAU_M=>S0001_G01_R40: OUTTAB[ /BI0/OIPOSTXT:NVARCHAR(5000) ]
 != expected result [ POSTXT:NVARCHAR(60) RECORD:NVARCHAR(56)
 SQL__PROCEDURE__SOURCE__RECORD:NVARCHAR(56) ]

Can anyone give me an idea of what I'm doing wrong here?

Answer 1

You might want to enclose the regex expression in a CAST( ... AS NVARCHAR(60)) to ensure that the resulting record structure matches the expected return type.

Answer 2

For those wondering how to correct this problem, here is the solution. Everything is in the error message:

OUTTAB[ /BI0/OIPOSTXT:NVARCHAR(5000) ]
 != expected result [ POSTXT:NVARCHAR(60) RECORD:NVARCHAR(56)
 SQL__PROCEDURE__SOURCE__RECORD:NVARCHAR(56) ]

It means the result table OutTab contains only one field (/BI0/OIPOSTXT) and so is different by the OutTab expected which should contain 3 fields POSTXT, RECORD and SQL__PROCEDURE__SOURCE__RECORD.

The expected structure can usually be seen on top of the public section:

  types:
    begin of TN_S_IN_S0001_G01_R1_1,
       POSTXT type C length 60,
       RECORD type C length 56,
       SQL__PROCEDURE__SOURCE__RECORD type C length 56,
    end of TN_S_IN_S0001_G01_R1_1 .

So the correct syntax would be:

outTab =
  SELECT CAST(REPLACE_REGEXPR('([^[:print:]|^[\x{00C0}-\x{017F}]|[#])' IN "SGTXT" WITH '' OCCURRENCE ALL) AS NVARCHAR(60)) AS "POSTXT"
    ,"RECORD" AS "RECORD"
    ,SQL__PROCEDURE__SOURCE__RECORD AS "SQL__PROCEDURE__SOURCE__RECORD"
    FROM :inTab;

Regards, Jean-Guillaume