Skip to Content

Nested CDS view / oData result

Feb 05 at 03:35 PM


avatar image

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!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Ankit Maskara
Feb 09 at 01:15 AM

may be try using Union clause

10 |10000 characters needed characters left characters exceeded
Mohamed Ahamed Ibrahim Abdul Kader Mar 02 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



Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.