Selecting a field from multiple fields returned by CDS view join

[nkhanolkar]

Hi. I have 2 tables A and B. I create a CDS view by joining table A to B using the common 4 keys between A and B. This returns me multiple records based on the 5th key column value of table B. I want to fetch the records with only the maximum key value of the 5th column, in my CDS view. How can I design such a CDS view? How would the query look like?

[nkhanolkar]

Hi. I have 2 tables A and B. I create a CDS view by joining table A to B using the common 4 keys between A and B. This returns me multiple records based on the 5th key column value of table B. I want to fetch the records with only the maximum key value of the 5th column, in my CDS view. How can I design such a CDS view? How would the query look like?

[BiberM]

Which record from B do you need? Or do you need an Aggregate of all corresponding record in B?

[Patrick_vN]

Something like this?

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW'
define view sales_order as
  select from snwd_so
  { key buyer_guid,
        currency_code,
        max(gross_amount) as max_gross_amount,
  }
  group by buyer_guid, currency_code

Just add your JOIN, and GROUP BY your 4 key fields..

[nkhanolkar]

Hi Patrick,

Thanks for your reply. So, for eg.

I have the following 2 tables with the columns as shown:

Table A

key char a1 | key char a2 | key char a3 | kf a4

Table B

key char b1 | key char b2 | key char b3 | key char b4 | kf b5

Now I do a join as below

define view CDSVIEW as select from a 
inner join b
on a.a1 = b.b1
and a.a2 = b.b2
and a.a3 = b.b3
  {
    key a.a1,
    key a.a2,
    key a.a3,
    kf a.a4,
    max(b.b4)as maxyr,
    b.b5
}
groupby a.a1, a.a2, a.a3, a.a4, b.b5

Here 'b.b4' is a Year characteristic. So it has multiple values. And so does 'b.b5'. 
So this join returns multiple records, out of which I need the record only for the latest year.

To further explain, if 'b.b4' has values '2017' and '2018' for the key, and corresponding kf values of 'b.b5' 
are '0.5' and '0.6' respectively, then group by will return both records for the join key. And I only want the
record for '2018' with kf '0.6'. How can that be achieved?! The source doesn't have a definite key column for 'b.b4'.

[Patrick_vN]

Then I guess you'll need to create an additional CDS to define/calculate the maximum year,..

define view CDSVIEWMAX as select from b
  { b.b1,
    b.b2,
    b.b3,
    max(b.b4)as maxyr
  }
group by b.b1, b.b2, b.b3

.. and add that one in the join of your final CDS. Something like this:

define view CDSVIEW as select from CDSVIEWMAX
inner join b
       on b.b1 = CDSVIEWMAX.b1
      and b.b2 = CDSVIEWMAX.b2
      and b.b3 = CDSVIEWMAX.b3
      and b.b4 = CDSVIEWMAX.maxyr
inner join a
       on a.a1 = CDSVIEWMAX.b1
      and a.a2 = CDSVIEWMAX.b2
      and a.a3 = CDSVIEWMAX.b3
  {
    key CDSVIEWMAX.b1,
    key CDSVIEWMAX.b2,
    key CDSVIEWMAX.b3,
    kf a.a4,
    CDSVIEWMAX.maxyr,
    b.b5
}

[nkhanolkar]

I just did that and it works for me!! Thanks to everyone who suggested a solution. I created 2 views as Patrick suggested above.