SQL 305 Error in ABAP CDS

[abhijitmoholkar]

Hi All,

I am working on S4 2023 on premise and investigating CDS views with error SQl 305 single-row query returns more than one row. 

I am CDS novice and my understanding is, this error occurs when a [0..1] association returns more than 1 row based on join condition and the main CDS is unable to choose a single record. Is this correct?

Just give an idea about CDS design -

My CDS view reads data from a standard CDS view with left outer joins on two other CDS views .

It then declares associations.

The  CDS view itself exposes fields from 2 associations and it's consumption view exposes fields from other associations.

Hopefully this gives an idea of CDS definition

The issue I am facing could not be replicated in Sandbox or Dev. What is the best way to investigate this error.

I tried ST05 and it did not give any help as per which statement is giving this error.

Thanks in advance for the guidance and help from the community.

Regards

Abhijit

[AlexandrePassarelli]

Hi,

Yes, your understanding is basically right. SQL error 305 on HANA is "single-row query returns more than one row", and in a CDS view it usually points to one thing: an association or join declared as to-one that, for some keys, finds more than one row in the target. HANA believes the cardinality you declare and builds the plan on top of it. When the data doesn't match, the ABAP documentation is very direct: "It is important that the cardinality specification matches the data in question. Otherwise, the result is undefined and can depend on the entries in the SELECT list."

That also explains why Sandbox and Dev are fine. The view is the same in every system, the data is not. It only breaks where a key really has duplicates in the target.

To be honest, I couldn't reproduce the 305 itself in my S/4HANA 2023 lab. But the "undefined result" part was easy to see. I joined MARA to MAKT on MATNR only, without the language, and declared MANY TO ONE. For a material with texts in 3 languages, COUNT(*) returned 1, because HANA dropped the join I had promised was to-one. Selecting MAKTX in the same query returned 3 rows. Without the cardinality, both gave 3.

SAP even ships a demo for this, DEMO_CDS_WRONG_CARDINALITY_1: an association from SCARR to SPFLI on CARRID only, with no cardinality written at all. For AC, AF and LH, counting per carrier gives 1 each, while reading CONNID through the same view gives 14 rows (LH alone has 12 flights).

Here is what I would check.

First, list every to-one association in your view and in the views below it. An association without an explicit cardinality is to-one by default ([0..1]), so the culprit may not even have a cardinality written.

Then compare each ON condition with the full key of the target. Text tables without the language and time-dependent data without a validity date are the classic cases.

Then, on the system where it fails, look for the duplicates directly. Use the target table and the fields of your ON condition:

SELECT matnr, COUNT(*) AS n
  FROM makt
  GROUP BY matnr
  HAVING COUNT(*) > 1

If this returns rows, you found it. It's the only test that tells the truth, because it runs on the data that breaks.

And get the full error from ST22 (DBSQL_SQL_ERROR) or, if the call comes through OData, from /IWFND/ERROR_LOG. The statement in the dump helps to see which association path is involved.

The fix is one of two: complete the ON condition (language, validity date, a filter in the path expression), or declare the real cardinality (to-many) and deal with the extra rows on purpose.

If you can share the ON condition and the cardinality of the associations involved, I'm happy to take a look.

[abhijitmoholkar]

Hi @AlexandrePassarelli 

Many thanks for your detailed reply. It certainly confirmed that my initial assessment was correct.

I have one question regarding your MARA - MAKT example - I thought a 0..1 association should have given you SQL 305 error in the scenario you mentioned, but it did not, am I missing something? 

Just to explain the whole CDS scenario (I have changed object names)

1. ZC_LOCANL_A - SQL View - Times out but no SQL 305 error
2. ZC_LOCANL - CDS definition for ZC_LOCANL_A - Times out but no SQL 305 error
3. ZI_LOCANL_A - CDS Source or Main view - SQL 305 error

ZI_LOCANL_A is defined as below. I hope this gives enough clarity to analyse. This is an existing CDS view (not developed by me) . 

define view entity ZI_LOCANL
as select from I_LocAnalCube
//used in case below
 left outer join ZI_EU on ZI_EU.BeginDate <= I_LocAnalCube.NotifCrdt
                         and ZI_EU.EndDate >= I_LocAnalCube.NotifCrdt
                         and I_LocAnalCube.CstCtr = ZI_EU.RelObjId

 left outer join I_WrkCtrBySemKey on I_WrkCtrBySemKey.plant = I_LocAnalCube.MainWcPlant
				   and on I_WrkCtrBySemKey.wrkctr = I_LocAnalCube.MainWc

//used in case below
association [0..1] to ZI_WcTF on I_ZI_WcTF.WctrId = I_WrkCtrBySemKey.WcIntId
				 and ZI_WcTF.BeginDate <= I_LocAnalCube.NotifCrdt
                         	 and ZI_WcTF.EndDate >= I_LocAnalCube.NotifCrdt

association [0..1] to ZI_MNotifXtra on ZI_MNotifXtra.MaintNotif = $projection.MaintNotif

//ZI_MaintBd reads from a table function and TF has no filter on MaintNotif
association [0..1] to ZI_MaintBd on ZI_MaintBd.MaintNotif = $projection.MaintNotif

association [0...*] to ZI_NotifEngChg on ZI_NotifEngChg.Char = $projection.MaintNotif
				       and ValidityEndDate = '99991231'

//used in case below
association [0..1] to ZI_EqOu on ZI_EqOu.Equipment = $projection.Equipment
				and ZI_EqOu_TF.VStartDate <= I_LocAnalCube.NotifCrdt
                         	 and ZI_EqOu_TF.VEndDate >= I_LocAnalCube.NotifCrdt 
	 
{
  key MaintNotif,
  ...//fields from I_LocAnalCube
  
  case when I_LocAnalCube.TechObj = 'EAMS_EQUI'
       then ZI_EqOu.Ref1
       else ZI_EU.Ref1   
  end as Ref1 

  case when I_LocAnalCube.TechObj = 'EAMS_EQUI'
       then ZI_EqOu.Ref2
       else ZI_WcTF.Ref2   
  end as Ref2 


  //exposed associations
  ZI_MNotifXtra
  ZI_MaintBd
  ZI_NotifEngChg

}

[AlexandrePassarelli]

@abhijitmoholkar you are not missing anything, I expected the same. So this morning I went back to my S/4HANA 2023 lab to see where 305 really comes from.

A to-one association doesn't raise 305 by itself. In the CREATE statement that the activation generates, it is only a join with a hint. SAP's view entity DEMO_CDS_WRONG_CARDINALITY_1 (association to SPFLI, no cardinality written) becomes LEFT OUTER MANY TO ONE JOIN, and DEMO_CDS_EXPLICIT_CARDINALITY with [1..*] becomes LEFT OUTER MANY TO MANY JOIN. HANA doesn't check that hint against the data. With duplicates you get extra rows or a wrong count, but no error. I ran MARA to MAKT again with LEFT OUTER MANY TO ONE JOIN for a material with 3 texts: 3 rows, no error.

305 needs a place where the database must return exactly one value: a scalar subquery. In ABAP SQL I got it with WHERE matnr = ( SELECT matnr FROM makt WHERE matnr = '000000000000000073' ): "SQL code 305, single-row query returns more than one row". With spras = 'E' added inside the subquery (one row), the same query worked.

So in the view you posted I don't see anything that can give 305 directly. I would look one level down: ZI_WcTF, ZI_EqOu_TF and the table function behind ZI_MaintBd. In their AMDP methods, look for a subquery in parentheses, in the field list or after an =, and check that it returns one row per key on the data where it fails.

Two things that can make the search shorter:

1. In ADT, right-click in the DDL source and use "Show SQL CREATE Statement". It shows what each association turned into, and you can repeat it for the views below until you find the subquery.
2. In the SQL console, select only the key plus one field at a time from ZI_LOCANL (Ref1, Ref2, a field through the ZI_MaintBd association). HANA leaves out a to-one join when none of its fields is selected (that was the COUNT(*) = 1 in my first test), so the field that brings the error back points to the object. That may also be why ZC_LOCANL times out without 305, if it doesn't read those fields. This last part is a guess, I didn't test it with your views.

What I didn't test: a single-value SELECT ... INTO inside SQLScript, which is the other common way to hit "more than one row" in a table function.