Hello all,
I need an calculation view with table function/UDF and three IP parameters for company code:
IP_BUKR_FROM and IP_BUKR_TO as array and
IP_BUKRS_MUL as parameter for multiple selection alternatively.
The query
SELECT * FROM "MYSCHEMA."MYFUNCTION"('201811','0','9999','363;364')
works fine,
SELECT * FROM "_SYS_BIC"."REWE.ZFI.BIBER.MABU-BIBER::ZFIMB_UDFENRICH_ABTBUMO18B"('201811','100','200','')
gives me this error message
Could not execute 'SELECT * FROM ...' in 24.169 seconds . SAP DBTech JDBC: [305]: single-row query returns more than one row: search table error: "MYSCHEMA"."MYFUNCTION": line 25 col 1 (at pos 732): [305] (range 3) single-row query returns more than one row exception: single-row query returns more than one row: ptime::PtimeException (please check lines: 25)
The same UDF is ok if I skip the two parameters for the select option (from to) and only leave the parameter IP_BUKRS_MUL in the code...
This is my UDF:
FUNCTION "MYSCHEMA"."MYFUNCTION" (IP_BUCHUNGSPERIODE VARCHAR(6), IP_BUKR_FROM VARCHAR(4), IP_BUKR_TO VARCHAR(4), IP_BUKRS_MUL VARCHAR(5000)) RETURNS TABLE ( R_BUCHUNGSPERIODE VARCHAR(6), R_FISCVARNT VARCHAR(2), R_SAP_KOSTENSTELLE NVARCHAR(10), R_CO_AREA NVARCHAR(4), R_BUKR VARCHAR(4), RK_AMOUNT DECIMAL(15,5)) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER DEFAULT SCHEMA ABAP AS BEGIN DECLARE BUKRS_FIL VARCHAR(5000) := ' BUKR in (' || :IP_BUKRS_MUL || ') '; IF (IP_BUKR_FROM IS NULL AND IP_BUKR_TO IS NULL) THEN IP_BUKR_FROM = '0'; IP_BUKR_TO = '9999'; END IF; var_sel = SELECT Buchungsperiode AS BUCHUNGSPERIODE , 'K4' AS FISCVARNT , SAP_KOSTENSTELLE_ AS SAP_KOSTENSTELLE , CO_AREA , BUKR , Amount AS AMOUNT from "OTHERSCHEMA"."MYTABLE" where Buchungsperiode = :IP_BUCHUNGSPERIODE AND BUKR >= :IP_BUKR_FROM AND BUKR <= :IP_BUKR_TO ; IF (IP_BUKR_FROM = '0' OR IP_BUKR_TO = '9999') THEN var_in = APPLY_FILTER(:var_sel, :BUKRS_FIL); ELSE var_in = SELECT * FROM :var_sel; END IF; RETURN SELECT BUCHUNGSPERIODE AS R_BUCHUNGSPERIODE, FISCPER AS R_FISCPER, FISCVARNT AS R_FISCVARNT, SAP_KOSTENSTELLE AS R_SAP_KOSTENSTELLE, CO_AREA AS R_CO_AREA, LPAD (BUKR, 4, '000') AS R_BUKR, AMOUNT AS RK_AMOUNT FROM :var_in; END;
Is there any solution with all three parameters working?
Best regards, Martin