SQL Transformation

如果你比较擅长写SQL，可以使用SQL Transformation

When you create an SQL transformation, you configure the following options:
¨ Mode. The SQL transformation runs in one of the following modes:

Script mode. The SQL transformation runsANSI SQL scriptsthat are externally located. You pass a script
name to the transformation with each input row. The SQL transformation outputs one row for each input row.
Query mode. The SQL transformation executes a query that you define in a query editor. You can pass
strings or parameters to the query to define dynamic queries or change the selection parameters. You can
output multiple rows when the query has a SELECT statement.

¨ Passive or active transformation. The SQL transformation is an active transformation by default. You can
configure it as a passive transformation when you create the transformation.

¨ Database type. The type of database the SQL transformation connects to.

¨ Connection type. Pass database connection information to the SQL transformation or use a connection object.

 

简单例子：

 

Select语句必须放在最前面，当有多条Select语句时，只有第一条起作用。You must configure an output port for each column in the SELECT statement. The output ports must be in the same order as the columns in the SELECT statement.

多条语句用分号分开。

select 语句是在mapping write to target 时候提交的，所以例子中的update语句其实只有更新了插入的insert语句，select作用的语句并没有update为9。

 

Using Static SQL Queries 例子（每行执行的SQL语句一样，只是query parameter不一样）：

SELECT Name, Address FROM Employees WHERE Employee_Num =?Employee_ID? and Dept =?Dept?  (Input ports 作为query parameters)

 

Using Dynamic SQL Queries例子（每行执行的SQL语句都不一样）：

SELECT Emp_ID, Address from ~Table_Port~ where Dept = ‘HR’ (Input port 作为动态SQL语句的一部分)

select HOSP_LEVEL_NAME ,
HOSP_CLASS_NAME ,
HOSP_MKT_NAME ,
LEVEMIR_HOSP_NAME ,
NOVOMIX50_HOSP_NAME ,
VICTOZA_HOSP_NAME ,
ORA_HOSP_FLG_NAME ,
INS_HOSP_FLG_NAME ,
DDD_FLG_NAME ,
DBD_TIER_NAME ,
EM_CITY_TYPE_NAME ,
EM_COUNTY_TYPE_NAME ,
EM_FLG_NAME ,
COUNTY_EN_TYPE_NAME ,
COMMUNITY_HOSP_TYPE_NAME ,
LEVEMIR_EXTHOSP_NAME
from (select /*+ use_hash(ex,pp) */
         ex.hosp_wid, pp.prop_type, /*pp.row_wid prop_wid,*/pp.prop_name 
          from tr_hosp_ext ex, td_prop pp
         where ex.hosp_prop_val = pp.prop_type
           and ex.hosp_prop_desc = pp.prop_id
           and ex.hosp_wid=?HOSP_WID?
            and ex.lang_id='en' and pp.lang_id='en'
           and hosp_prop_val in
               ('HOSP_LEVEL', 'HOSP_CLASS', 'HOSP_MKT',  'LEVEMIR_HOSP','NOVOMIX50_HOSP',
                'VICTOZA_HOSP','ORA_HOSP_FLG', 'INS_HOSP_FLG',/*'MATERNITY_TYPE',*/ 'DDD_FLG','DBD_TIER','EM_CITY_TYPE', 'EM_COUNTY_TYPE', 'EM_FLG',
                'COUNTY_EN_TYPE','COMMUNITY_HOSP_TYPE','LEVEMIR_EXTHOSP')) 
     /* pivot(sum(prop_wid) as wid for(prop_type) in ('HOSP_LEVEL' as HOSP_LEVEL, 'HOSP_CLASS' as HOSP_CLASS, 'HOSP_MKT' as HOSP_MKT, 'NOVOMIX50_HOSP' as NOVOMIX50_HOSP, 'VICTOZA_HOSP' as VICTOZA_HOSP, 'MATERNITY_TYPE' as MATERNITY_TYPE, 'DDD_FLG' as DDD_FLG, 'EM_FLG' as EM_FLG, 'COUNTY_EN_TYPE' as COUNTY_EN_TYPE))*/
      pivot(MAX(prop_name) as name for(prop_type) in ( 'HOSP_LEVEL' as HOSP_LEVEL, 
                                                       'HOSP_CLASS' as HOSP_CLASS, 
                                                       'HOSP_MKT' as HOSP_MKT, 
                                                       'LEVEMIR_HOSP' as LEVEMIR_HOSP,
                                                       'NOVOMIX50_HOSP' as NOVOMIX50_HOSP, 
                                                       'VICTOZA_HOSP' as VICTOZA_HOSP, 
                                                       'ORA_HOSP_FLG' as ORA_HOSP_FLG,
                                                       'INS_HOSP_FLG' as INS_HOSP_FLG,
                                                      /* 'MATERNITY_TYPE' as MATERNITY_TYPE, */
                                                       'DDD_FLG' as DDD_FLG,
                                                       'DBD_TIER' as DBD_TIER,
                                                       'EM_CITY_TYPE' as EM_CITY_TYPE ,
                                                       'EM_COUNTY_TYPE' as EM_COUNTY_TYPE,
                                                       'EM_FLG' as EM_FLG,
                                                       'COUNTY_EN_TYPE' as COUNTY_EN_TYPE,
                                                       'COMMUNITY_HOSP_TYPE' as COMMUNITY_HOSP_TYPE,
                                                       'LEVEMIR_EXTHOSP' as LEVEMIR_EXTHOSP ))