Group and Merge data from multiple rows table in CDS

[minnalmurali]

hello Guys,

My requirement is to get partner data and its contracts and then get the corresponding appointment dates of those contracts in SAP CRM tables. So now I have created CDS to retrieve the partner and contract details, and now I have to retrieve the dates from the appointment table. The problem is I need to retrieve multiple dates for each contract how can I achieve this in CDS view.

eg: My current CDS will retrieve something like this (not exactly like below but just for explanation purpose 🙂 )

but now for each contract, I need to retrieve dates that are stored in the SCAPPTSEG table with appointment types as below,

Now I would like to get the dates in different columns for each item, the output should look like this.

Can we do it in the same CDS view?

Note: The date types are not dynamic. For each contract, only 5 types of dates need to retrieve.

After assigning the values using CASE for the new fields, I tried GROUP BY, but it won't work, since I need to show multiple fields it's not grouping into a single line.

[minnalmurali]

I have fixed the issue by creating another CDS view and inner join with my existing view.

• Create the first CDS view to retrieve contract details.
• Create another CDs view to retrieve the status from SCAPTSEG table
• In this get the status from the SCAPTSEG table and GROUP by GUID and then use CASE and MAX( ) function to put the different statuses in different columns. Since its GROUP BY using the guid the end result will be single line with all status.
• Then do an inner join with the first CDS view .

Code for the second view to get the status

as select from  crmd_link as link  
left outer join scapptseg as app  on  app.appl_guid = link.guid_set
 { link.guid_hi as linkguid,
max ( case  app.appt_type when 'ISTCONTSRTTS'  then substring(cast(app.tst_from as abap.char(17) ), 1, 8 ) end ) as START_TIMESLICE,
max ( case  app.appt_type when 'ISTCONTENDTS'  then substring(cast(app.tst_from as abap.char(17) ), 1, 8 ) end ) as END_TIMESLICE,
max ( case  app.appt_type when 'ISTRUNTMEND'   then substring(cast(app.tst_from as abap.char(17) ), 1, 8 ) end ) as CONTRACT_END_TERM }