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 ))

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

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

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

Spark常用transformation和action操作

1,场景 spark的transformation和action操作乃spark的核心,让我们走进他们,领略他们的魅力! 2,transformation和action介绍 Spark支持...

台湾大学林轩田机器学习基石课程学习笔记12 -- Nonlinear Transformation

上一节课,我们介绍了分类问题的三种线性模型,可以用来解决binary classification和multiclass classification问题。本节课主要介绍非线性的模型来解决分类问题。一...

【OpenCV】透视变换 Perspective Transformation(续)

这篇文章是我从自己的QQ(632846506)日志中移过来的。https://user.qzone.qq.com/632846506/infocenter。     透视变换的原理和矩阵求解...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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