SQL Transformation

How to use SQL Transform in SAP BODS?

SQL Transformation:

This transform is used to perform SQL operation retrieve the data from one or multiple table.

When we use: Sometimes it may not be possible or complex to design ETL with complex Business logic.

It is very easy to build. We need the SQL select statement.

Create a new PROJECT and a JOB as shown in below screen

Bring the SQL Transform into Data flow.

Paste the below simple SQL code inside the transform.

select e.empno,e.ename,e.sal,d.dname from emp e ,dept d where d.loc in (‘NEW YORK’,‘DALLAS’) and e.deptno = d.deptno and e.empno in (select e.empno

from emp e where e.job in (‘MANAGER’,‘ANALYST’) and e.comm is null)

order by d.loc asc;

Select the Data Store Name and Database type

Keep the other options as shown in screen

Click on Update schema

Change the data type of the column names.

In this case, EMPNO shows data type as decimal. But we can change to int.

Use the Query transform and Template table as the destination.

SAVE the work.

Run the JOB.

Target table’s data after the JOB run.