topic Re: ABAP CDS select distinct values in Application Development and Automation Discussions

ABAP CDS select distinct values

former_member192842 — Fri, 03 Mar 2017 04:01:12 GMT

Hi Experts,

I need a help in ABAP CDS. The want to write a Join or association statment in ABAP CDS. Consider the below table A and B

I want output to be

The input for me is column 1(value 1).

In case of ABAP I could have written 2 select queries and then looped table A and could have read the table B to create final output.

But in case of ABAP CDS is there way to achieve it. Because If I write Join/Association in ABAP CDS with value 1, then my output could be two rows below which is not same as the output mentioned above

1 Fed Makati

1 Fed BGC

Regards Anand

Re: ABAP CDS select distinct values

retired_member — Fri, 03 Mar 2017 07:23:29 GMT

You want to restrict the result set of a CDS View to one line (an arbitrary or the first ?).

To my knowledge, CDS DDL does not offer this. But you can use UP TO 1 ROWS when accessing your view with Open SQL.

Re: ABAP CDS select distinct values

Former Member — Tue, 30 Jan 2018 09:34:31 GMT

Hi, there are two ways:

a) Using a table function cds accessing table A and B through open SQL;

b) Creating an inner join between table A and table B on a.column1 = b.column1 where a.column1 = '1' => output as you created before, then a.column1 , a.column2, max(b.column2) group by a.column1, a.column2

Re: ABAP CDS select distinct values

roy_pappachan2 — Thu, 15 Feb 2018 15:14:49 GMT

we can use open SQL 'LIMIT' right?