CDS UNION not working as supposed to

[Victorcardona]

Hello SAP expert,

I am having a problem with a union between two cds. after making the union, the original records from the source cds are modified and the resulting union is changing the data. For example, for one specific record in the source cds the period is equal to 202306, but after the union the same record appers with periodo equal to 202212.

The union cds query is the following:

@AbapCatalog.sqlViewName: 'CCDSMBEWMBEW'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@ObjectModel.usageType.serviceQuality: #A

@ObjectModel.usageType.sizeCategory: #L

@Analytics.dataExtraction.enabled: true

@ObjectModel.usageType.dataClass: #TRANSACTIONAL

@VDM.viewType: #CONSUMPTION

@Analytics.dataCategory: #CUBE

@EndUserText.label: 'Union de tablas mbew y mbewh'

define view C_CDS_MBEW_MBEWH as select from C_CDS_INV_PRD_TER_21 as mbewh

{

Material,

Periodo,

AmbitoVal,

ClaVal,

CatValo,

@DefaultAggregation: #SUM

StockTot,

@DefaultAggregation: #SUM

PreMedVal,

@DefaultAggregation: #SUM

PreEstand,

@DefaultAggregation: #SUM

ValTot,

'mbewh' as identificador

}

union all select from C_CDS_INV_PRD_TER_23 as mbew

{

Material,

Periodo,

AmbitoVal,

ClaVal,

CatValo,

@DefaultAggregation: #SUM

StockTot,

@DefaultAggregation: #SUM

PreMedVal,

@DefaultAggregation: #SUM

PreEstand,

@DefaultAggregation: #SUM

ValTot,

'mbew' as identificador

}

As you can see I am making an union between two cds views. Next you will find the code for the two corresponding cds that I am making the union with:

C_CDS_INV_PRD_TER_23

@AbapCatalog.sqlViewName: 'CINVPRDT23' @AbapCatalog.compiler.compareFilter: true @AbapCatalog.preserveKey: true @AccessControl.authorizationCheck: #CHECK @EndUserText.label: 'INV PRD TERMINADO Y MP' @ObjectModel.usageType.serviceQuality: #A @ObjectModel.usageType.sizeCategory: #L @Analytics.dataExtraction.enabled: true @ObjectModel.usageType.dataClass: #TRANSACTIONAL @VDM.viewType: #CONSUMPTION @Analytics.dataCategory: #CUBE define view C_CDS_INV_PRD_TER_23 as select from mbvmbew { matnr as Material, lfmon as Mes, lfgja as Anio, concat (lfgja,lfmon) as Periodo, bwkey as AmbitoVal, bwtar as ClaVal, bklas as CatValo, @DefaultAggregation: #SUM lbkum as StockTot, @DefaultAggregation: #SUM verpr as PreMedVal, @DefaultAggregation: #SUM stprs as PreEstand, @DefaultAggregation: #SUM salk3 * 100 as ValTot }

C_CDS_INV_PRD_TER_21

@AbapCatalog.sqlViewName: 'CINVPRDT21'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'INV PRD TERMINADO Y MP'

@ObjectModel.usageType.serviceQuality: #A

@ObjectModel.usageType.sizeCategory: #L

@Analytics.dataExtraction.enabled: true

@ObjectModel.usageType.dataClass: #TRANSACTIONAL

@VDM.viewType: #CONSUMPTION

@Analytics.dataCategory: #CUBE

define view C_CDS_INV_PRD_TER_21 as select from mbvmbewh

{

matnr as Material,

lfmon as Mes,

lfgja as Anio,

concat (lfgja,lfmon) as Periodo,

bwkey as AmbitoVal,

bwtar as ClaVal,

bklas as CatValo,

@DefaultAggregation: #SUM

lbkum as StockTot,

@DefaultAggregation: #SUM

verpr as PreMedVal,

@DefaultAggregation: #SUM

stprs as PreEstand,

@DefaultAggregation: #SUM

salk3 * 100 as ValTot

}

Could you please explain me why is this happeing and how can I correct teh cds to show the union with the same original data from the source cds without any change? Thank you very much

[filipn]

Hello,

UNION ALL takes all data from the first CDS view and puts all data from the second CDS view under it. If there are 100 records in the first CDS view and 100 records in the second CDS view, there will be 200 records in the final view, including all duplicate records.

UNION ALL does not change any data during their merger.

Regards,

Filip