SAP DWC Built-in Library SQLSCRIPT_STRING

former_member841186 — Fri, 10 Feb 2023 08:44:22 GMT

I'm attempting to use the the built-in library SQLSCRIPT_STRING in a SQLScript (Table Function) view on SAP DWC (see Split string into multiple rows using SQL in SAP HANA | SAP Blogs). I'm wondering whether this is supported as I'm receiving an "Identifier expected" error for the USING statement. Is using built-in libraries supported in SAP DWC views? Thank you!

USING SQLSCRIPT_STRING AS LIB;

DECLARE TEST_OUTPUT TABLE(RESULT NVARCHAR(5000));
	
DECLARE TEST_STRING VARCHAR(50) := 'A,B,C,D,E,F,G,H';

TEST_OUTPUT = LIB:SPLIT_TO_TABLE(:TEST_STRING,',');
	
RETURN_OUTPUT = SELECT * FROM :TEST_OUTPUT;

RETURN :RETURN_OUTPUT;

Re: SAP DWC Built-in Library SQLSCRIPT_STRING

Ivan_Camac — Sun, 12 Feb 2023 12:30:57 GMT

According to the SQL Functions Reference for DWC, I don't believe DWC supports the same built-in libraries. You could perhaps write your own equivalent as a table function that could be called In your code.

Maybe something like:

FUNCTION "_SYS_BIC"."ZBW.CSIRO_PROJECT.Functions::TF_SPLIT_STRING" ( INPUT_STRING VARCHAR(5000), DELIM VARCHAR(1) DEFAULT ',', TRIMSTART BOOLEAN DEFAULT TRUE ) 
	RETURNS TABLE
	(
		"OUTPUT_SPLIT" VARCHAR(5000)
	)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN
	IF TRIMSTART = TRUE THEN
		INPUT_STRING = TRIM ( LEADING :DELIM FROM INPUT_STRING);
	END IF;

	IF LOCATE(:INPUT_STRING,:DELIM) = 0
	
	THEN
		SPLIT_VALUES = SELECT :INPUT_STRING AS SINGLE_VAL FROM DUMMY;
		
	ELSE
		SPLIT_VALUES = SELECT SUBSTR_BEFORE(:INPUT_STRING,:DELIM) AS SINGLE_VAL FROM DUMMY;
	
		SELECT SUBSTR_AFTER(:INPUT_STRING,:DELIM) || :DELIM INTO INPUT_STRING FROM DUMMY;
	
		WHILE( LENGTH(:INPUT_STRING) > 0 )	
		DO
			SPLIT_VALUES =
				SELECT SINGLE_VAL FROM :SPLIT_VALUES
				UNION
				SELECT SUBSTR_BEFORE(:INPUT_STRING,:DELIM) AS SINGLE_VAL FROM DUMMY;
				
	
			SELECT SUBSTR_AFTER(:INPUT_STRING,:DELIM) INTO INPUT_STRING FROM DUMMY;
	   	
		END WHILE;
	
	END IF;
	
	RETURN
	
	SELECT SINGLE_VAL AS "OUTPUT_SPLIT" FROM :SPLIT_VALUES; 

END

The above is taken from a BW/4HANA implementation, not DWC so not suggesting it would work straight off.

topic Re: SAP DWC Built-in Library SQLSCRIPT_STRING in Application Development and Automation Discussions

SAP DWC Built-in Library SQLSCRIPT_STRING

Re: SAP DWC Built-in Library SQLSCRIPT_STRING