Skip to Content
avatar image
Former Member

Nested CDS view / oData result

I am trying to merge two tables (by ID) and return the data as a nested json object via oData, but got no luck so far. Since I couldn't find any useful resources, I am starting to wonder if it's even possible to do.
I'm using Eclipse Oxygen to code the CDS-Views, and already tried different SQL joins, but they only generate multiple sets of data, instead of one nested object. I attached images with some sample data I made in Exel, so you can understand my problem a bit better.

Example of what I am trying to achieve:
First Table (CustomerDetails):

Second Table (CustomerMails):

Expected result (as JSON):

I only added the first 3 entries into the JSON, but you get what I mean.

JSON Overview:

The actual result looks something like this (same ID multiple times):

Code of my CDS-View (unrelated to the data above):

define view ZXZTI_CUSTOMER as select from kna1 as k
left outer join adr6 as a on k.adrnr = a.addrnumber
{ true
    key k.kunnr as CustomerNumber, true
    k.name1 as CustomerName, true
    k.land1 as CustomerCountry,
    @Semantics.address.zipCode: true
    k.pstlz as CustomerZipcode, true
    k.ort01 as CustomerCity,
    @Semantics.address.street: true
    k.stras as CustomerStreet,
    @Semantics.eMail.address: true
    a.smtp_addr as CustomerEMail

Thanks in advance!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Feb 09, 2018 at 01:15 AM

    may be try using Union clause

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 02, 2018 at 05:25 AM


    I too face the exactly the same problem. i am trying to develop CDS view for Profit center lookup. I want all the company details to be attached to single profit center with out all the data get repeated . Did you get any breakthrough on the above problem that you have mentioned?.

    any help will be highly appriciated



    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      No, I couldn't solve the problem so far. Still looking for an useful solution. I made some workarounds, but they aren't the way you should do it. (For example getting all results, and filtering them with JS clientside)

      I would appreciate any help on this topic too.