Skip to Content

Display Database Content and Run SQL Queries

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
julieplummer20Julie PlummerJuly 28, 2022

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)
  • Tutorial: Create an ABAP Project in ABAP Development Tools (ADT)
  • 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

    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
  • Step 2

    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
  • Step 3

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

    select Columns
  • Step 4

    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
  • Step 5

    Press Add Filter and choose CURRENCY_CODE.

    data filter
  • Step 6

    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
  • Step 7

    Click the SQL Console button to open the SQL Console.

    open sql console
  • Step 8

    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
  • Step 9

    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:

    sql
    Copy
    SNWD_SO_INV_ITEM join SNWD_SO_INV_HEAD
    on SNWD_SO_INV_ITEM~parent_key = SNWD_SO_INV_HEAD~node_key
    
    join condition
  • Step 10

    Modify the WHERE clause to match the following:

    sql
    Copy
    SNWD_SO_INV_ITEM~CURRENCY_CODE = 'USD'
    
    where clause
  • Step 11

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

    select list
  • Step 12

    Click Run to execute the current query or press F8.

    run button
  • Step 13

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

    results
  • Step 14

    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.

    sql
    Copy
    join SNWD_BPA
    on SNWD_SO_INV_HEAD~buyer_guid = SNWD_BPA~node_key
    
    updated select
  • Step 15

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

    sql
    Copy
    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
  • Step 16

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

    sql
    Copy
    ORDER BY SNWD_BPA~company_name
    
  • Step 17

    Click Run to execute the current query or press F8.

    run button
  • Step 18

    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
  • Step 19

    What parts of the SELECT statement can you generate in the Data Preview? Select all that apply:

Back to top