topic Re: Is it possible to do nested select on DDL / SQL? in Application Development and Automation Discussions

Is it possible to do nested select on DDL / SQL?

former_member679084 — Fri, 15 May 2020 12:26:59 GMT

I have a problem with the nested select, I just want to select the text when custtype = B,

can you suggest an approach that I can try to implement for this solution? Thank you!

the 1st one is SQL console,

2nd one is CDS DDL

Re: Is it possible to do nested select on DDL / SQL?

maheshpalavalli — Fri, 15 May 2020 13:43:43 GMT

Hi mchua9,

It's currently not possible in ABAP CDS, but you an do this in an another way using left outer join. check out the code below:

@AbapCatalog.sqlViewName: 'ZVSBOOKSTEST'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'test'
define view zsbookstest as select from sbook 
                           left outer join sbuspart
                                        on sbuspart.buspartnum = sbook.customid
                                       and sbook.passname is initial {
    sbook.bookid,
    case when passname is initial then sbuspart.contact else passname end as passname
}

-Mahesh

Re: Is it possible to do nested select on DDL / SQL?

former_member679084 — Sat, 16 May 2020 08:13:53 GMT

ok thanks, that's too bad then..

this is now my working code. it seems like when you add "and sbook.passname isinitial" from outside the clause runtime is slower though its just around ~10-15ms per 5000 line on 10 runs I have tried

Re: Is it possible to do nested select on DDL / SQL?

maheshpalavalli — Sun, 17 May 2020 06:09:05 GMT

great!, I didn't know adding where clause of is initial in join will slow it donw,, i thought we will be filtering unrequired data.. Thanks for the tip.

You can close the question if your query is answered.

https://blogs.sap.com/2017/08/15/what-to-do-with-my-answered-questions/