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.