ABAP CDS getting count of multiple tables in one CDS

[VinodB1]

Hello Experts,

Is it possible to get the count of entries from multiple tables (with no linkage) into a single CDS view.

Actually we are trying to use this CDS for reconciliation purposes and send this information to non SAP data lake to compare the count of records.

Thank You.

Vinod

[Sandra_Rossi]

What did you try? Depends on your context, ABAP release, RAP, etc. Possibilities that you may explore: "IMPLEMENTED BY METHOD" with AMDP class, annotation @ObjectModel.query.implementedBy, or the evil one CDS view per "table" doing a COUNT(*) plus one CDS view with cross join of these "count" CDS views to sum the totals.

[VinodB1 · Accepted solution]

Thank yo all for the reply.

As mentioned tried to resolved the issue with Union All, and it worked.

As mentioned by @Sandra_Rossi the output looks exactly same. I have added more tables now to get the count of records against table as an output.

Code : 

<General anntaions>

@EndUserText.label: 'Count Recon Test'
define view ZTEST_COUNT_VB1 as
select from mara as a {
'MARA' as TableName,
count(*) as RecordCount
}
union all select from makt as b {
'MAKT' as TableName,
count(*) as RecordCount
}
union all select from kna1 as c {
'KNA1' as TableName,
count(*) as RecordCount
}
union all select from cdpos as d {
'CDPOS' as TableName,
count(*) as RecordCount
}

One can go on adding tables along with Union All statement.

Thank You

Vinod

[keremkoseoglu]

You can do that with AMDP easily. If you must have a CDS interface, you can prepare the CDS in the form of a table function and write AMDP behind it.

[VinodB1]

Thank You All for your reply. I just tried with below code (Union All) and seems like it worked without AMDP and Table Function. Please provide your feedback if see any issue

@AbapCatalog.sqlViewName: 'ZTESTCOUNTVB1'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Count Recon Test'
define view ZTEST_COUNT_VB1 as select from mara as a {
'MARA' as TableName,
count (*) as RecordCount
}
union all select from makt as b {
'MAKT' as TableName,
count (*) as RecordCount
}
union all select from kna1 as c {
'KNA1' as TableName,
count (*) as RecordCount
}

[Sandra_Rossi]

@VinodB1Why not posting your comment as a separate answer and mark it as the solution to the question?

You forgot to say that your query gives a result with 3 lines e.g.

TableName	RecordCount
MARA	500
MAKT	500
KNA1	120