Skip to Content

Display Database Content and Run SQL Queries

test
0 %
Display Database Content and Run SQL Queries
Details

Display Database Content and Run SQL Queries

2020-02-19
Beginner
15 min.
ABAP Development, Tutorial, Beginner
Use Data Preview to display the content of tables and SQL Console to perform ad-hoc SQL queries

You will learn

  • How to display the content of tables and views using Data Preview
  • How to perform ad hoc SQL queries using SQL Console

Prerequisites

  • You have a valid instance of an on-premise AS ABAP server, version 7.51 or higher (some ABAP Development Tools may not be available in earlier versions)
  • You have generated the data for the relevant database table by running the transaction SEPM_DG_OIA_NEW (or program RS_EPM_DGC_HANA_NEW ).

The tutorial starts from scratch, based on tables of the SAP NetWeaver Demo sample EPM Model (you can find details in the SAP Community The NetWeaver Enterprise Procurement Model – An Introduction ), so you can repeat the tutorial on any SAP NetWeaver 7.5 system.

Step 1: Open the Data Preview

In ABAP Development Tools (ADT), open the database table snwd_so_inv_item by choosing Execute ABAP Development Object (or using Alt+F8).
This will open the Data Preview which will allow you to explore the invoice items table.

abap development object
Done
Log on to answer question
Step 2: Select a database table

Enter snwd_so_inv_item in the search field. Once the search has delivered a result or if the table is already in the history list, select **SNWD_SO_INV_ITEM** and choose OK.

run abap development object
Done
Log on to answer question
Step 3: Data preview

In the Data Preview Click Select Columns to change which fields are retrieved from the database.

select Columns
Done
Log on to answer question
Step 4: Select columns

First click Deselect All. Only select the fields GROSS_AMOUNT and CURRENCY_CODE. Click OK to confirm your selection.

selecting Columns

The Data Preview is refreshed accordingly.

step4-data preview
Done
Log on to answer question
Step 5: Add a filter

Press Add Filter and choose CURRENCY_CODE.

data filter
Done
Log on to answer question
Step 6: Enter filter criteria

Enter USD in the text field to filter the items that are in US dollars and press the Enter key.

step6-filter-usd

The Data Preview is refreshed accordingly.

step6b-result
Done
Log on to answer question
Step 7: Open the SQL Console

Click the SQL Console button to open the SQL Console.

open sql console
Done
Log on to answer question
Step 8: Initial SQL SELECT statement

The SQL Console shows the SELECT statement which was used to retrieve the data for the Data Preview. In the following steps you will adjust the SELECT statement to read the payment status of the order and the company name from the buyer.

step7-curr-code-usd
Done
Log on to answer question
Step 9: Add a JOIN condition
View Full Instructions

Adjust the SELECT statement in SQL Console in order to get the payment status from the order header by doing the following: Add a JOIN condition for the table snwd_so_inv_head
NOTE: You may get an error. You will solve this in the next step.

The FROM portion of the query should look like this:

SNWD_SO_INV_ITEM join SNWD_SO_INV_HEAD
on SNWD_SO_INV_ITEM~parent_key = SNWD_SO_INV_HEAD~node_key
join condition
Done
Log on to answer question
Step 10: Update the WHERE clause
View Full Instructions

Modify the WHERE clause to match the following:

SNWD_SO_INV_ITEM~CURRENCY_CODE = 'USD'
where clause
Done
Log on to answer question
Step 11: Add a field to the SELECT list

Add the field payment_status from the table snwd_so_inv_head to the SELECT list.

select list
Done
Log on to answer question
Step 12: Run the query

Click Run to execute the current query or press F8.

run button
Done
Log on to answer question
Step 13: Check the query results

Check that the output on the right hand side contains the payment status.

results
Done
Log on to answer question
Step 14: Adjust the SELECT statement
View Full Instructions

Adjust the SELECT statement again in order to retrieve the company name from the table snwd_bpa by adding a JOIN condition for the table snwd_bpa.

join SNWD_BPA
on SNWD_SO_INV_HEAD~buyer_guid = SNWD_BPA~node_key
updated select
Done
Log on to answer question
Step 15: Adding the company name field
View Full Instructions

Add the field company_name from the table snwd_bpa to the SELECT list.

SNWD_BPA~company_name,
SNWD_SO_INV_ITEM~GROSS_AMOUNT,
SNWD_SO_INV_ITEM~CURRENCY_CODE,
SNWD_SO_INV_HEAD~PAYMENT_STATUS
add company name to select
Done
Log on to answer question
Step 16: Add an order by clause
View Full Instructions

Add an ORDER BY clause to sort the result by the company name of the buyer.

ORDER BY SNWD_BPA~company_name
Done
Log on to answer question
Step 17: Run the modified query

Click Run to execute the current query or press F8.

run button
Done
Log on to answer question
Step 18: Review updated results

Check that the output contains the company name. You will reuse this SELECT statement later for data retrieval in an ABAP class. So just leave the SQL Console open.

step18-run-button
Done
Log on to answer question
Step 19: Test yourself
What parts of the SELECT statement can you generate in the Data Preview? Select all that apply:
×

Next Steps

Navigate tutorial steps

QR Code

QR code
Back to top