Issue Fetching Data from ANEK table using CDS views

[#1]

We are trying to fetch data from ANEK table on S4 via database sql / CDS View. This fetch doesnt fetch any data. The runtime properties of ANEK table show 0 entries. When we use SE16 or SE11 to display data, it does display the data.

Can you please let me know how I can fetch ANEK table data via CDS View or Database View.

[AlexandrePassarelli]

Hi,

ANEK is empty on the database in S/4HANA. What SE16 shows you is not the table, it is a CDS view that SAP puts in front of it for ABAP SQL. I checked it on two S/4HANA systems, 2020 and 2023, and both behave the same way.

Why SE16 shows data and your CDS view does not

• In DD02L, ANEK has a replacement object: FAAV_ANEK, the SQL view of the CDS view FAA_ANEK ("compatibility view for table ANEK"). ANEP, ANEA, ANLP and ANLC have the same thing: FAAV_ANEP, FAAV_ANEA, FAAV_ANLP and FAAV_ANLC.
• FAA_ANEK builds the ANEK lines from ACDOCA, joined with BKPF and with the reporting ledger of the company code. Its first SELECT reads the real ANEK with WHERE bukrs = '0001' AND bukrs = '0002', which is never true. The comment in the source says it is there only to give the view the field types of the old table.
• A replacement object works only for ABAP SQL reads, and SE16 reads with ABAP SQL, so it gets the redirected data. The ABAP keyword documentation, page "DDIC Replacement Objects", says that a CDS view, a database view, AMDP and Native SQL that use the table still read the table itself: "The replacement object must be accessed directly."

What I measured, row counts in one client:

• ABAP SQL, SELECT COUNT(*) FROM anek: 20 on 2023, 197 on 2020
• Native SQL (ADBC) on table ANEK: 0 on both
• ABAP SQL on the CDS view FAA_ANEK: 20 and 197
• Native SQL on its SQL view FAAV_ANEK: 20 and 197
• ABAP SQL on FAA_ANEK_ORI, a CDS view delivered by SAP that is only "select * from anek": 0 on both. This is your case.

So the "0 entries" in the runtime properties is correct. It is the database table, and the table is empty.

What you can do

1. In ABAP code, a plain ABAP SQL SELECT FROM anek works. The redirect does the job.
2. If you read the HANA database directly (I saw the BW/4HANA tag, so maybe from the BW side), select from the SQL view FAAV_ANEK instead of the table ANEK. It is in the same schema as the table, and native SQL on it gave the same count as ABAP SQL on ANEK.
3. In your own CDS view, use Faa_Anek as the data source instead of anek. It has the same field names and types. On 2023 I ran the syntax check of a view entity that selects from Faa_Anek and it had no errors (only syntax checked, not activated). It is not a released API, there is no release state for it in that system, so this is for classic ABAP on premise and not for ABAP Cloud.
4. For a stable source, read the asset lines from the released view I_JournalEntryItem, contract C1. MasterFixedAsset, FixedAsset, AssetTransactionType, AssetValueDate, AssetDepreciationArea, AccountingDocument, PostingDate and DocumentDate are there. ACDOCA keeps asset lines per ledger and depreciation area, so filter one ledger and one area, or you will get the same document more than once. FAA_ANEK does this with the reporting ledger and the leading area. The ANEK line number LNRAN is not in I_JournalEntryItem: FAA_ANEK takes it from ACDOCA-SUBTA, and SUBTA is commented out in that view.

What I did not test: a field by field comparison between FAA_ANEK and I_JournalEntryItem, and how FAA_ANEK performs on a big ACDOCA. It is a UNION ALL with several joins and a SELECT DISTINCT, and the view is annotated with size category XL.