Skip to Content

ABAP CDS select distinct values

Mar 03, 2017 at 04:01 AM


avatar image
Former Member

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

table-a.jpg (12.8 kB)
table-b.jpg (13.7 kB)
output.jpg (11.8 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Horst Keller
Mar 03, 2017 at 07:23 AM

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.

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

we can use open SQL 'LIMIT' right?

Pierfrancesco La Spada Jan 30 at 09:34 AM

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

10 |10000 characters needed characters left characters exceeded