Navigating the Database Landscape: Joins Unveiled in SAP - A Practical Guide

 Welcome back everyone, We have started to discuss about joins in the last blog, So let’s continue it.

---

In our last blog, we were using for all entries, and for using for all entries, We had seen that we had to write multiple select queries to fetch data from multiple tables. Now we will see how we can get data from multiple tables using joins.

---

Q. Whether we should use for all entries or we should use joins to get data from multiple tables ?

Ans :- When we are working on traditional database systems (like Oracle, DB2, Sybase ), we should use for all entries in i.e. We will write individual queries to fetch data from multiple tables.

But for HANA database we should prefer Joins.

Q. Why we should not use Joins in traditional databases ?

Ans :- It is because when we use joins, it fetches data from multiple tables and because of that load on the traditional database system is high and therefore, we should prefer for all entries in but HANA is a very powerful database and we can use joins on it without any problem.

---

How to check which database we are using?

• Open your SAP system.

• Click on System and then click on status.

  
  
  

  
  

• In the Database area, you can see which database you are using.

  
  
  

  
  

---

Practical Implementation of Inner Join :-

• Suppose, We have 6 data ( from 1 to 6 ) in the Order header table and 5 data from (1 to 5 ) in the item table, So when we use inner join, It will simply fetch the 5 data which are present in both the tables.

Requirement :-We will take order number as input and show the details of order number as output.

Steps :-

• Step 1 :- Follow the below code to create the type structure and declare internal table and work area.

  
  
  

  
  

• Step 2 :- Create a selection screen box, where we will take input for order number.

  
  
  

  
  

• Step 3 :- Perform Select Queries to fetch data from multiple tables.

  
  
  

  
  

  • You can see in the above program, we have aliases.
  • Also, when you refer to a field of table we use tiled(~) operator.

• Step 4 :- Loop on internal table to display the data.

  
  
  

  
  

• Step 5 :- Execute the program and enter order number

  
  
  

  
  

• Step 6 :- Execute the program to see the output.

  
  
  

  
  

Code :-

***********************************************************
*Start of Program
*Declaring the final type structure
TYPES: BEGIN OF ty_final,
         order_number	     TYPE zar_order_number,
         order_date	       TYPE zar_order_date,
         payment_mode	     TYPE zar_payment_mode,
         total_amount	     TYPE zar_total_amount,
         currency	         TYPE zar_curency,
         order_item_number TYPE zar_order_item_number,
         item_cost         TYPE zar_total_amount,
       END OF ty_final.
*********************************************************
*Declaring the internal table and work area
DATA : lt_final TYPE TABLE OF ty_final,
       ls_final TYPE ty_final.
********************************************************
****************************************
*Defining Selection Screen
DATA : lv_order TYPE zar_order_number.
SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS : s_order FOR lv_order OBLIGATORY.

SELECTION-SCREEN : END OF BLOCK b1.
*end of Selection Screen
*************************************************************

*****************************************
*Select queries along with inner join
SELECT a~order_number a~order_date a~payment_mode a~total_amount a~currency b~order_item_number b~item_cost
  FROM zar_header AS a INNER JOIN zar_item AS b ON a~order_number = b~order_number
  INTO TABLE lt_final WHERE a~order_number IN s_order.

IF lt_final IS NOT INITIAL.
*******************************************
*Displaying the data
  LOOP AT lt_final INTO ls_final.
    WRITE :/ ls_final-order_number,
             ls_final-order_date,
             ls_final-payment_mode,
             ls_final-total_amount,
             ls_final-currency,
             ls_final-order_item_number,
             ls_final-item_cost.
  ENDLOOP.

ENDIF.

***********************
*End of Program
*****************************************************************************

---

So, that’s enough for today,

We will see about left outer join in our next blog.

Thanx alot for being a part of this wonderful journey.