SQL Transformation
来源:互联网 发布:讯捷数据恢复 编辑:程序博客网 时间:2024/06/05 16:07
如果你比较擅长写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
- 预告 | SQL 优化之 transformation
- 预告 | SQL 优化之 transformation
- 干货 | SQL优化之SQL transformation
- Transformation
- SSIS lookup transformation http://www.katieandemil.com/ssis-lookup-transformation-example-sql-server
- SQL优化【基础09】 - 查询转换(query transformation)
- Rotation transformation
- affine transformation
- lookup transformation
- Computer Transformation
- Reassociation Transformation
- OpenGL Transformation
- Affine Transformation
- Shanks transformation
- Image Transformation
- OpenGL Transformation
- 浅谈Access数据库的缺陷以及数据库应该直接存储图片还是存储图片路径
- ubuntu系统启动时自动进入root用户,此处列出10.04和12.04的方法
- 设置交叉工具链路径
- 总结linux下find命令
- iOS开发系列--通讯录、蓝牙、内购、GameCenter、iCloud、Passbook详解
- SQL Transformation
- 任务调度5-------Spring中使用timetask执行定时任务
- 启用MySQL查询缓存
- jQuery事件代理的应用
- iOS开发 AFNetWork "Request failed: unacceptable content-type: text/html"
- Cell上边或者下边出现不规则长度的黑色细线
- SQLite的WAL机制
- APP微信支付集成
- Device Tree Usage