How to use Lookup and Join in SAP Data Services

This article is part of our comprehensive data services tutorial Learning SAP Data Services - Online Tutorial, you may want to check that tutorial first if you have not already done so.

Data for illustration

For the purpose of this tutorial, we will use 3 tables namely,

• EMP - This table contains employee information including employee number, employee name, department number, salary etc.
• DEPT - This is the master table for Departments. It basically contains department number and department name
  
• SALGRADE - This table contains salary grades based on a range of salary
  

How to perform Join in data Services

Given the above data sets, let us assume we want to generate below results by joining EMP and DEPT table.

EMPNO   ENAME   DNAME
7839    King    Accounting
7369    Smith   Research
.... .... ....

If we want to obtain the above set using plain SQL, we will write an SQL statement like below joining both the tables using outer join

Select e.empno, e.ename, d.dname
From Emp e Left Outer Join Dept d
On e.deptno = d.deptno

In order to perform the same join in Data Services, we will take advantage of Query Transform. Query transform is a multipurpose transform in data services that basically imitate an SQL statement and lets you do varied things including joining, aggregation, filtering etc.

We will use query transform here to establish the join between EMP and DEPT table as shown in the screenshot below

Please see the video at the bottom of this article to go-through the individual steps in Data Services

How to perform Lookup in Data Services

Theoretically a lookup is similar to a SQL function call. A function takes one or multiple values as input and returns one (or multiple) value as output. In the video below, we have demonstrated how you may use a data services lookup.

Data Services Hands-on Video

Here is a quick video that show you the steps on how to perform Join and/or lookup in BODS.