topic Re: Selecting a field from multiple fields returned by CDS view join in Application Development and Automation Discussions

Selecting a field from multiple fields returned by CDS view join

nkhanolkar — Wed, 10 Feb 2021 10:43:10 GMT

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?

Re: Selecting a field from multiple fields returned by CDS view join

BiberM — Wed, 10 Feb 2021 17:43:50 GMT

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

Re: Selecting a field from multiple fields returned by CDS view join

Patrick_vN — Wed, 10 Feb 2021 17:50:58 GMT

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..

Re: Selecting a field from multiple fields returned by CDS view join

nkhanolkar — Wed, 10 Feb 2021 18:25:08 GMT

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'.

Re: Selecting a field from multiple fields returned by CDS view join

Patrick_vN — Wed, 10 Feb 2021 18:31:33 GMT

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
}

Re: Selecting a field from multiple fields returned by CDS view join

nkhanolkar — Wed, 03 Mar 2021 12:41:47 GMT

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