SQL Transformation

原创 2015年07月08日 17:41:26

如果你比较擅长写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 ))

SQL transformation

SQL transformation how can we convert single row to multiple row (example) SAP_SALES_ORD_NUM C...

[SQL2005]SSIS 使用 OLE DB Command Transformation 组件

SQL2005-SSIS 使用 OLE DB Command Transformation 组件 这篇文章就给出如何利用 OLE DB Command Transformation 组件为每一个输入...

-Hauseholder and Givens Transformation

  • 2012年12月07日 23:26
  • 2.39MB
  • 下载

【Spark】RDD操作详解2——值型Transformation算子

 【Spark】RDD操作详解2——值型Transformation算子 处理数据类型为Value型的Transformation算子可以根据RDD变换算子的输入分区与输出分区关系分为以下...

开源库Fab-Transformation简单使用解析

转载请注明出处王亟亟的大牛之路类似于IPhone的悬浮按钮的操作,只不过是固定的,当然经过自己的修改也可以动,这边只是给伸手党一个福祉,外加加上一些自己的理解,让大家可以拿来就用,看了就懂,废话不多说...

Advanced Sap Bw Data Transformation

  • 2008年09月19日 11:18
  • 1011KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Transformation
举报原因:
原因补充:

(最多只允许输入30个字)