Skip to Content
0
Jul 06, 2020 at 09:42 AM

Error "single-row query returns more than one row"

3538 Views

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