Skip to Content

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

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on Jul 08, 2020 at 05:03 AM

    Hi Martin

    in that case, the problem is likely caused by a combination of what you do in that calculation view and the data you're fetching.

    For the error to occur there must be some part of the view that expects that only a maximum of 1 record can be returned.

    Something like

    SELECT a.ID
          , a.NAME
          , (SELECT b.RATE FROM rates b WHERE b.thing_name = a.name) as THING_RATE
    FROM 
       THINGS a

    would work fine, as long as there is only one RATE entry for each THING.NAME.

    As soon as your filter allows for entries that could return more than a single entry you'll see the error that you encounter now.

    A common way to deal with this is to make sure that there really cannot be more than one record returned. Using aggregate functions (e.g. MAX/MIN) could be an option here.

    Another way of producing such an error would be to something like this in SQLScript:

    SELECT b.RATE 
           INTO _thing_rate
    FROM rates b WHERE b.thing_name = a.name;

    Here, the same explanation as before applies.

    In short: this error is not due to using UDFs or calculation views (or any other development artifact type), but due to a misunderstanding of your data(-model).

    Cheers,

    Lars

    Add a comment
    10|10000 characters needed characters exceeded

    • This is the solution, many thanks again! I just added an INNER JOIN and the whole script now looks like this:

       DECLARE lv_flag INT DEFAULT 0;		
       DECLARE BUKRS_FIL VARCHAR(5000) := ' BUKR in (' || :IP_BUKRS_MUL || ') ';		
      
      IF (LENGTH(IP_BUKRS_MUL) > 0) 	
        THEN 		
          lv_flag = 1;
          var_in = APPLY_FILTER( "MYSCHEMA"."MYCV2" , :BUKRS_FIL);    -- variable var_in with filter 
          var_sel1 =                                                  -- variable var_sel1 with SELECT on var_in 
          SELECT 
            Buchungsperiode, 
            BUKR
          FROM :var_in 
            where Buchungsperiode = :IP_BUCHUNGSPERIODE
          GROUP BY
            Buchungsperiode, 
            BUKR; 
      ELSEIF (IP_BUKR_FROM > '0' AND IP_BUKR_TO < '9999') 
        THEN
          lv_flag = 1;
          var_sel1 = 
          SELECT 
            Buchungsperiode, 
            BUKR
          FROM "MYSCHEMA"."MYCV2" 
            where 
                Buchungsperiode = :IP_BUCHUNGSPERIODE 
            AND BUKR BETWEEN :IP_BUKR_FROM                     
                         AND :IP_BUKR_TO
          GROUP BY
            Buchungsperiode, 
            BUKR; 
      END IF; 
      
      IF lv_flag = 1 THEN 		         	
      var_sel2 =                                                   -- variable var_sel2 with SELECT on var_sel1 
        SELECT a.Buchungsperiode AS BUCHUNGSPERIODE               
          , 'K4' AS FISCVARNT
          , a.SAP_KOSTENSTELLE AS SAP_KOSTENSTELLE                   
          , a.CO_AREA AS CO_AREA                                     
          , a.KONTO AS KONTO                                         
          , a.BUKR AS BUKR                                            
          , a.AMOUNT AS AMOUNT
      FROM "MYSCHEMA"."MYCV2" a
      INNER JOIN :var_sel1 b
        ON a.BUKR = b.BUKR 
        AND a.Buchungsperiode = b.Buchungsperiode;
      END IF; -- lv_flag = 1;<br>
  • Posted on Jul 21, 2020 at 09:58 PM

    On additional remark to this issue I would like to add: very confusingly the error message "single-row query returns more than one row" also appears, when the result set is to large - in the above given solution e.g. when I select to many company codes. It took a lot of reflection and attempts, until I realized, that my code is now correct and I simply had to select a smaller range of company codes...

    Add a comment
    10|10000 characters needed characters exceeded

    • That would be a bug.
      The error message is about a qualitative error (multiple results records instead of a unique one) not a quantitative one (some, more, many or too many) records.

      If the error occurs with a wider selection but not with a narrow one, then the records left out in the narrow selection must contain the data that leads to the problem.