AMDP - Subselect Error: single row subquery returns more than one row

[former_member695585]

Good morning 🙂

I would like to do a subselect but im getting always this error: single" row subquery returns more than one row"

test = select
                          ta.rclnt,
                          ta.szenario,
                          ta.fiscalyear,  /*Fiscal Year*/
                          ta.version,                  /*Version*/
                          ta.period,                              /*Periode*/
                          ta.rcomp,                                 /*Company Code*/
                          ta.RBUKRS,                             /*Buchungskreis*/
                          ta.DRCRK,
                          CASE WHEN fagl.ERGSL LIKE_REGEXPR '[A-Z]' THEN
                                            (Select par.ERGSL from FAGL_011PC as par where par.ID in(
                                                (select distinct chi.parent from FAGL_011PC as chi where chi.ERGSL = fagl.ERGSL and chi.VERSN = 'IAT'))) ELSE fagl.ERGSL END as GAccount,        /*Transaction type*/


                          fagl.ERGSL,
                          fagl.XSOLL,
                          fagl.XHABN,                               /*Group-Account*/
                          ta.ktoks,
                          ta.rmvct,
                          ta.SAKNR,                                 /*G/L Account*/
                          ta.rassc,                                 /*Trading Partner*/
                          ta.fid_maturity,                /*Maturities*/
                          ta.hsl,
                          ta.FID_TRAN_TYPE
                    from :tagetik_response_PRCTR as ta
                    inner join FAGL_011ZC as fagl on fagl.VERSN = 'IAT' and fagl.VONKT = ta.SAKNR;

This select returns the error:

select distinct chi.parent from FAGL_011PC as chi where chi.ERGSL = fagl.ERGSL and chi.VERSN = 'IAT'

But if i hardcode the value "fagl.ERGSL" it is working

select distinct chi.parent from FAGL_011PC as chi where chi.ERGSL = 'PX23113AAA' and chi.VERSN = 'IAT'

It is a bug?
Normaly it should work.

Regards

Adrian

[former_member30]

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't already), as it provides tips for preparing questions that draw responses from our members.

For example, you can outline what steps you took to find answers (and why they weren't helpful) and share screenshots of what you've seen/done. The more details you provide, the more likely it is that members will be able to assist you. Should you wish, you can revise your question by selecting Actions, then Edit (although once someone answers your question, you'll lose the ability to edit the question -- but if that happens, you can leave more details in a comment).

Finally, if you're hoping to connect with readers, please consider adding a picture to your profile. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS. By personalizing your profile with a photo of you, you encourage readers to respond.

Cheers,
Julia
SAP Community Moderator

[KonradZaleski]

It's not a bug. As the error message says, your subquery returns more than one value which is not allowed for the window function. Even if you fixed the PX23113AAA value and it worked, it does not mean that for other values you will always get only 1 record.

There might be some other values in ERGSL column which are causing that this query:

Select par.ERGSL from FAGL_011PC as par

Returns more than one value. To make sure that always 1 record is returned you could add LIMIT 1 clause to your subquery:

Select par.ERGSL from FAGL_011PC as par where par.ID in(                                       (select distinct chi.parent from FAGL_011PC as chi where chi.ERGSL = fagl.ERGSL and chi.VERSN = 'IAT') ) LIMIT 1

But this may give you wrong CASE WHEN results in case there will be multiple values returned for ERGSL column (SQL engine will randomly pick up the first one)

Instead of using subquery it would be better if you just join this subquery to the current statement.