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

[martin_lehmann4]

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

[lbreddemann]

In your scenario, is "OTHERSCHEMA"."MYTABLE" indeed a plain table or is maybe a view with its own filter/selection criteria?

[martin_lehmann4]

Hi Lars, your are right - the identifier MYTABLE is a misleading identifier: it's another calculation view with further complex transformations and selections... What is really confusing is the fact, that this table function works fine as routine (whithout return table) - also in the debugger.

Should I try to execute all selections in this UDF?

[lbreddemann · Accepted solution]

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

[martin_lehmann4 · Accepted solution]

Hallo Lars,

many thanks for answering - I am with you in the necessity to aggregate the results in order to get single rows - but in this case we must deal with many rows because we need the results from n costcenters, m company codes and so on... Is there the only possibility to use loops for n, m and o (if there are other details to obtain) on the single row results?

Or is it better option to query the same calculation view twice: first aggregated with the filter and this joined with a second query with the details?

Best regards, Martin

[lbreddemann · Accepted solution]

I don't know:

• your code (in the "MYTABLE" view)
• your data
• your expected result

Thus, I cannot tell you how to write your report.

What I do know is that there are ways to cater for those cases where a single result record is not correct.

Which one to choose really depends on the points mentioned.

[martin_lehmann4 · Accepted solution]

Hi Lars,

• > your code (in the "MYTABLE" view)
  MYTABLE is a calculation view and would better be named MYCV (there are two of them):
  MYCV1 is an aggregation on the transactional data from the sales stores, which comes out of a SDA on Teradata db.
  It has filters in it on fiscal period and area to keep the data volume smaller.
  MYCV2, which has MYCV1 as data source, is also an aggregation and transposes separate key figures from Teradata (per cost elements) into one key figure per G/L account.
• > your data
  I try to give an example and apply therefor your proposed approach - in the comments I try to explain, which kind of data is provided. But when I execute this in my UDF (SQL in SQL Console) it gives me unfortunately the same "single-row query returns more than one row: search table error"....

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; 
IF (IP_BUKR_FROM = '0' OR IP_BUKR_TO = '9999') 
  THEN 
    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; 
ELSE 
    var_sel1 = 
    SELECT 
      Buchungsperiode, 
      BUKR 
    FROM "MYSCHEMA"."MYCV2" 
      where Buchungsperiode = :IP_BUCHUNGSPERIODE 
      AND BUKR >= :IP_BUKR_FROM 
      AND BUKR <= :IP_BUKR_TO; 
END IF; 
var_sel2 =                                                 -- variable var_sel2 with SELECT on var_sel1 
SELECT a.Buchungsperiode AS BUCHUNGSPERIODE                -- single record acc. to :IP_BUCHUNGSPERIODE 
, 'K4' AS FISCVARNT
, a.SAP_KOSTENSTELLE AS SAP_KOSTENSTELLE                   -- multiple values 
, a.CO_AREA AS CO_AREA                                     -- single record 
, a.KONTO AS KONTO                                         -- multiple values of above mentioned G/L account 
, a.BUKR AS BUKR                                           -- multiple values according BUKR variables above 
, (SELECT b.AMOUNT FROM : var_sel1 b                       -- according to your proposal 
WHERE a.BUKR = b.BUKR 
AND a.Buchungsperiode = b.Buchungsperiode) AS AMOUNT
from "MYSCHEMA"."MYCV2" a;

• > your expected result
  A composite provider reads the CV with table function (UDF) and selects several G/L accounts and calculations with these G/L-accounts via Query.

Many thanks for your help!
Martin

[lbreddemann · Accepted solution]

It looks like that what I wanted to convey with my explanation didn't fully get through...

My "proposal" wasn't one. Instead, the code snippets were meant to explain how the error occurs.
These snippets don't contain the solution.

In the code example you provided, the sub-select tries to get AMOUNT from the table variable var_sel1.
But this table variable never gets assigned a query where AMOUNT is included. This will lead to a compilation error.

Let's assume that there was a column AMOUNT included for now. Then we see that you filter on BUKR and BUCHUNGSPERIODE. Therefore, BUKR and BUCHUNGSPERIODE would have to form a UNIQUE key to the records "in" table variable var_sel1 - otherwise, there might be duplicates and the sub-query would return multiple records (-> that's the error again).

As the error occurs we know what that BUKR and BUCHUNGSPERIODE point to multiple AMOUNTs. Given the application context, one possible way to "fix" the error here is to aggregate the AMOUNTs for BUKR and BUCHUNGSPERIODE combinations.

Effectively, this creates a new aggregation level for this data.

The code for this could look like this:

IF (IP_BUKR_FROM = '0' OR IP_BUKR_TO = '9999') 
  THEN 
    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,
      SUM(AMOUNT) as SUM_AMOUNT
    FROM :var_in 
      where Buchungsperiode = :IP_BUCHUNGSPERIODE
    GROUP BY
      Buchungsperiode, 
      BUKR; 
ELSE 
    var_sel1 = 
    SELECT 
      Buchungsperiode, 
      BUKR,
      SUM(AMOUNT) as SUM_AMOUNT 
    FROM "MYSCHEMA"."MYCV2" 
      where 
          Buchungsperiode = :IP_BUCHUNGSPERIODE 
      AND BUKR BETWEEN :IP_BUKR_FROM                     -- just use BETWEEN instead of => AND <= 
                   AND :IP_BUKR_TO
    GROUP BY
      Buchungsperiode, 
      BUKR; 
END IF; 

On another note: the IP_BUKR_FROM/TO logic seems rather weird and you may want to rethink it.

First, it's checked if BOTH values are NULL and if so, they are set to '0' and '9999' respectively.
This BUKR-range is supposedly chosen to cover all BUKR values; which in-turn would equal to not filtering on BUKR at all.

But then, it's checked if one of the values is set to its respective "default" value. If so, the low/high BUKR range values are ignored, and instead, the filter string in IP_BUKR_FIL gets used.

My guess is that the expected behavior is:
Whenever there is some value in IP_BUKR_FIL use this filter, otherwise use the IP_BUKR_FROM/TO values.
In case either of those FROM/TO values is NULL, set it to the "fallback" value and use those values then.

I would caution against being overly forgiving/automating with those filter values.

Implementing this makes the logic flow harder to follow (and optimize) and the semantics of this behavior is not at all equal across all procedures, functions, and views.
Consumers of this function cannot rely on the behavior without looking at the code; practically speaking this means this feature should not be relied upon. Therefore: leave it out. If no BUKR filter values are provided at all, then the function will just not return anything, which is something easily discoverable by the consumer.

Alternatively, one could just declare default values for the parameters (check the SQLscript docu for details), which means there could always be non-NULL values for the FROM/TO values, but without the need for IF statements.

[martin_lehmann4 · Accepted solution]

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>

[martin_lehmann4]

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...

[lbreddemann]

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.