CDS View entity union all: take the value of a field from table EINA to table EINE

[tafkap95]

Hello, I am new to CDS, I want to extract data from two tables in a view: EINA and EINE.

For each EINE line we have an EINA line, the reverse is not true.

To do this I use the UNION ALL statement.

My need is very simple, in the EINA table we have the MATNR field, but not in the EINE table, so in my CDS view I would like for each EINE line to take the associated value from EINA.MATNR to my CDS view.

Here is my example code:

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'EINA/EINEE Union'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}

define view entity ZC_EinaEineUnion as select from eina
{
  key eina.infnr,
  'EINA' as TableSource,
   eina.matnr
}
union all select from eine
{
  key eine.infnr,
  'EINE' as TableSource,
  '' as matnr
}

When executed it gives:

My need: in the matnr column, I would like to have for all EINE lines the value '000000000007047609'.

Thank you in advance for your help

[M-K]

Why do you want to do an union select?

Just select the entries from EINE and do an inner join to EINA on INFNR.

[tafkap95]

I also want the row in the EINA table...

[M-K]

Then you can use your union select, and use the select from EINA for the first and select EINE inner join EINA for the second part.

[tafkap95]

That's exactly what I did, are you sure you read my post carefully? My problem is on the MATNR field of the EINE table.

[M-K]

I think of something like that:

define view entity ZC_EinaEineUnion as select from eina
{
  key eina.infnr,
  'EINA' as TableSource,
   eina.matnr
}
union all select from eine
inner join eina on eina.infnr = eine.infnr
{
  key eine.infnr,
  'EINE' as TableSource,
  eina.matnr
}

[tafkap95]

The solution is: 

@AbapCatalog.sqlViewName: 'ZVE_EINA_EINE'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Union EINA and EINE CDS View'
define view entity ZC_EinaEineUnion as select from eina
{
  key eina.infnr,
  'EINA' as TableSource,
  eina.matnr
}
union all
select from eine as e
  inner join eina as i on i.infnr = e.infnr
{
  key e.infnr,
  'EINE' as TableSource,
  i.matnr
}

Many thanks to GPT-4o 😉

[Kavitha-V]

Why you need to do Union or Union all?

You can perform Association between EINE and EINA tables by specifying condition on "infnr" field.

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Demo view'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
    serviceQuality: #X,
    sizeCategory: #S,
    dataClass: #MIXED
}
define view entity zcds_eine_eina as select from eine
association [0..* ] to eina as _eina on eine.infnr = _eina.infnr
{
    eine.infnr as Infnr,
    _eina.matnr as Matnr
}

You will get the result as follows ...

[tafkap95]

but in your result the line that comes from EINA is missing...