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

[former_member679084]

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

[former_member679084]

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

[maheshpalavalli]

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

[former_member679084]

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

[maheshpalavalli]

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/