longer need a database user to program the database procedures, it has become increasingly easy to access data from different database schema using SQL script by implementing AMDP methods.
The syntax to accessdata from an underlying database schemas is : –
Please note the specific difference between SQL script syntax and the Open SQL. We have to mention the Physical Schema Name to correctly identify the underlying path to access the required data of that <table>. If you do not give the physical schema, then the default schema is automatically chosen. The default schema can be found using function module – DB_DBSCHEMA_CURRENT.
Now, you would have the below questions:
- Why are we talking about different database schemas?
- What is the problem if I just use the direct Open SQL or CDS to fetch the data?
- I have written AMDP selection without physical schema name, then what is this that you are talking about?
- As an ABAP programmer, why and when would I be required to pull data using such DB procedures?
Well, from my learning – not all the tables in the underlying database schemas have their corresponding dictionary view. Hence, you cannot see them all in SE11 / SE16, and such tables can exist which hold relevant business master / transaction data. The source of these data could be any SAP / non-SAP system and using SAP LT replication technique, these tables are populated.
Ok, now coming to the real purpose of this blog.
Let’s say that youwant to access table ZCCARD_DETAILS (table holding credit card details of the customer)which is in HANA database but does not have dictionary view and is in a physical schema different from thedefault.
You have been told by the basis person that the physical schema name in which this table is lying is: DEV_SCHEMA whereas the schema name would change in quality as QUAL_SCHEMA and in production as PROD_SCHEMA(different schema name in different system is the regular practice, nothing new ? ).
Now, as per the abovesyntax, you would write below AMDP code: –
This will work perfectly fine in development but when you will move to quality, it is going to fail since there is no such physical schema called “DEV_SCHEMA”. In quality the physical schema is QUAL_SCHEMA.
To tackle this problem, there is something called Schema Mapping Concept – which is again an underlying database table in physical schema “_SYS_BI”, and it holds the alias for each physical schema. Alias remains the same in all systems, but the names of physical schema attached against it change.
So, in developmentsystem, the schema mapping entries would look like this: –
|ALIAS ( called as AUTHORING SCHEMA or logical name)||Physical Schema|
And the same would bein quality:
|ALIAS ( called as AUTHORING SCHEMA )||Physical Schema|
Hurray, now you just have to refer to Alias name in AMDP select query in below way: –
- Get the physical schema based on Alias -> First select query
- Get the card details by appropriately referring the physical schema name that you got in step 1 in the second select query.
This means, that youhave to dynamically pass the physicalschema name to the second select query.
But, is there any SQL script syntax which supports passing such dynamic reference of the schema name ??? Well, not in my knowledge. ?
Then, are we stuck?What can we do now?
The answer to this is:AMDP’s standard delivered macro called $ABAP.Schema.
This macro autoconverts the alias into physical schema name and then directly places it intothe SELECT query where it is being used. The syntax is: –
Excellent!! With this syntax, you do not have to write 2 selects queries and also the problem of passing dynamic physical name is resolved, since you have to just pass the logical name and macro takes care of the rest.
Also please check my other Article – Code Quality Control, Simplified
Is that all? Well NO!!
There are some actionsto be performed even before we can use this syntax.
First of all, let me tell you that I used logical name as ZS4 but did not use the alias name ZS4_ALIAS that I showed you. Now, what is this ZS4? Where is this coming from?
ZS4 is the logical database schema and can be created using Eclipse ADT. Open the project, click NEW object. Move to Others -> Define logical schema definition. Follow the wizard and then activate it.
Choose: Other ->Logical Database Schema.
Here you create thelogical schema ZS4. After following all the steps in the wizard, you willfinally get the below screen: –
This screen tells you that you have now mapped the underlying physical schema name in the transaction DB_SCHEMA_MAP. Please activate the logical schema before you go to the transaction, else it will not be visible there.
In the transaction DB_SCHEMA_MAP, you will see that the entry with logical name ZS4 has appeared.
Choose the record,click on EDIT, place the physical schema name and click on SAVE. You can chooseto also transport this logical name to further systems.
But remember, though you move the logical schemaname via transport, the attachment to the physical schema name in transactionDB_SCHEMA_MAP has to be done in the target system directly. This becomes a cutover activity.
After maintaining theschema name, you can re-verify it in the logical database schema in ADT. Youwill now see as below: –
This finishes all theactivity.
Note: The AMDP can be activated only when the above steps are done. Untilthen, you will keep getting syntax error stating that the requiredconfiguration is not done and AMDP will not be activated.
You do tons of SAP ABAP on HANA training. You read lots of S/4HANA Technical blogs. But no one can teach you better than the real project issues and use cases. I have been working on HANA ABAP for few years now, but accessing the database schema dynamically gave me an initial set back. Hopefully, you would not have to spend any time in finding the solution.
We have a very active Telegram (App) SAP Technical Group with more than 4250+ SAP Technical Practitioners from 6 Continents of the SAP World. Please join it using below link.
Telegram SAP Technical Discuss Group. You need to install the Telegram App first on your mobile device. Once you have it on your mobile, you can join the group and also access it from the Web on your computer and laptop.
Free SAP ABAP for HANA Training Tutorials Exercises.
- ABAP on SAP HANA. Part I. First Program in ABAP HANA
- ABAP on SAP HANA. Part II. ADT Eclipse and HANA Studio
- ABAP on SAP HANA. Part III. Debugging in ADT
- CDS Part 1. Core Data Services – Introduction
- CDS Part 2. Core Data Services – Deep Dive
- ABAP on SAP HANA. Part VI. New Age Open SQL ABAP 740
- ABAP on SAP HANA. Part VII. SQL Script and SAP HANA Stored Procedure
- ABAP on SAP HANA. Part VIII. ADBC – ABAP DataBase Connectivity
- ABAP on SAP HANA. Part IX. AMDP – ABAP Managed Database Procedure
- ABAP on SAP HANA. Part X. AMDP with SELECT OPTIONS
- ABAP on SAP HANA. Part XI. Are Native SQL and Open SQL Competitors?
- ABAP on SAP HANA. Part XII. Open SQL, CDS or AMDP, which Code to Data Technique to use?
- ABAP on SAP HANA. Part XIII. Sample Functional Specification of HANA Project
- ABAP on SAP HANA: Part XIV. HANA Ready, HANA-tization & HANA Plus
- CDS Part 3. Expose CDS Views as OData Service through Annotation
- ABAP on SAP HANA: Part XVI. HANAtization
- ABAP on SAP HANA: Part XVII. ATC – ABAP Test Cockpit Setup & Exemption Process
- SAP ABAP on HANA: Part XVIII. SALV IDA (Integrated Data Access)
- ABAP for SAP HANA. Part XIX. Sample Technical Specification of HANA Project
- ABAP for SAP HANA. Part XX. ALV Report On SAP HANA – Opportunities And Challenges
- 4 Useful Tips on ABAP and ABAP on HANA
- Associations in HANA – A Conceptual Approach
- CDS Part 5. ABAP CDS Views With Authorization
- ABAP for SAP HANA. Part XXII. How to Consume Native HANA Views Using ADBC?
- CDS Part 6. Basic Expressions & Operations Available for CDS View – I
- 115 ABAP for SAP HANA Interview Questions & Answers
- CDS Part 7. Basic Expressions & Operations Available for CDS View – II
- CDS Part 8. Usage of Built-In Functions in CDS – I
- CDS Part 9. Usage of Built-In Functions in CDS – II
- CDS Part 10. Usage of Built-In Functions in CDS – III
- CDS Part 11. How to Consume CDS View in Smart Business Service KPI Fiori Apps?
- CDS Part 12. Useful 6 CDS Related Tools in ADT
- CDS Part 13. Key Definition in CDS Views
- CDS Part 14. ABAP Annotations for Translatable Texts in CDS Views
- CDS Part 15. Associations in CDS Views – I
- ABAP for SAP HANA – Part 23. How to Access Database Schema Dynamically Using AMDP?