Skip to Content

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

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

Add a comment
10|10000 characters needed characters exceeded

  • 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

Related questions

1 Answer

  • Posted on Aug 07, 2020 at 01:39 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.