DB2中几个递归语句的案例
来源:互联网 发布:韩国漫画软件下载 编辑:程序博客网 时间:2024/06/05 10:03
DB2递归算法:
1、查找某个作业的下游作业:
WITH GET_STREAM_JOB(LEVEL,ETL_SYSTEM,ETL_JOB) AS (
SELECT 0,ETL_SYSTEM,ETL_JOB FROM PETL.ETL_JOB_STREAM WHERE ETL_JOB='XXX'
UNION ALL
SELECT LEVEL+1,A.STREAM_SYSTEM,A.STREAM_JOB FROM PETL.ETL_JOB_STREAM A,GET_STREAM_JOB B
WHERE A.ETL_SYSTEM=B.ETL_SYSTEM AND A.ETL_JOB=B.ETL_JOB
)SELECT * FROM PETL.ETL_JOB WHERE (ETL_SYSTEM,ETL_JOB) IN (SELECT ETL_SYSTEM,ETL_JOB FROM GET_STREAM_JOB)
;
2、查找某个作业的的所有下游作业
WITH GET_STREAM_JOB(LEVEL,ETL_SYSTEM,ETL_JOB) AS (
SELECT 0,ETL_SYSTEM,ETL_JOB FROM PETL.ETL_JOB_DEPENDENCY WHERE DEPENDENCY_JOB='XXX' AND DEPENDENCY_SYSTEM='XXX'
UNION ALL
SELECT LEVEL+1,A.ETL_SYSTEM,A.ETL_JOB FROM PETL.ETL_JOB_DEPENDENCY A,GET_STREAM_JOB B WHERE A.DEPENDENCY_SYSTEM=B.ETL_SYSTEM
AND A.DEPENDENCY_JOB =B.ETL_JOB
)SELECT MIN(T2.LEVEL),T1.ETL_SYSTEM,T1.ETL_JOB,T1.DESCRIPTION,T1.LAST_JOBSTATUS,T1.JOBTYPE FROM PETL.ETL_JOB T1,GET_STREAM_JOB T2
WHERE T1.ETL_SYSTEM=T2.ETL_SYSTEM AND T1.ETL_JOB=T2.ETL_JOB AND T1.JOBTYPE<>'M'
GROUP BY T1.ETL_SYSTEM,T1.ETL_JOB,T1.DESCRITTION,T1.LAST_JOBSTATU,T1.JOBTYPE
ORDER BY MIN(T2.LEVEL)
;
1、查找某个作业的下游作业:
WITH GET_STREAM_JOB(LEVEL,ETL_SYSTEM,ETL_JOB) AS (
SELECT 0,ETL_SYSTEM,ETL_JOB FROM PETL.ETL_JOB_STREAM WHERE ETL_JOB='XXX'
UNION ALL
SELECT LEVEL+1,A.STREAM_SYSTEM,A.STREAM_JOB FROM PETL.ETL_JOB_STREAM A,GET_STREAM_JOB B
WHERE A.ETL_SYSTEM=B.ETL_SYSTEM AND A.ETL_JOB=B.ETL_JOB
)SELECT * FROM PETL.ETL_JOB WHERE (ETL_SYSTEM,ETL_JOB) IN (SELECT ETL_SYSTEM,ETL_JOB FROM GET_STREAM_JOB)
;
2、查找某个作业的的所有下游作业
WITH GET_STREAM_JOB(LEVEL,ETL_SYSTEM,ETL_JOB) AS (
SELECT 0,ETL_SYSTEM,ETL_JOB FROM PETL.ETL_JOB_DEPENDENCY WHERE DEPENDENCY_JOB='XXX' AND DEPENDENCY_SYSTEM='XXX'
UNION ALL
SELECT LEVEL+1,A.ETL_SYSTEM,A.ETL_JOB FROM PETL.ETL_JOB_DEPENDENCY A,GET_STREAM_JOB B WHERE A.DEPENDENCY_SYSTEM=B.ETL_SYSTEM
AND A.DEPENDENCY_JOB =B.ETL_JOB
)SELECT MIN(T2.LEVEL),T1.ETL_SYSTEM,T1.ETL_JOB,T1.DESCRIPTION,T1.LAST_JOBSTATUS,T1.JOBTYPE FROM PETL.ETL_JOB T1,GET_STREAM_JOB T2
WHERE T1.ETL_SYSTEM=T2.ETL_SYSTEM AND T1.ETL_JOB=T2.ETL_JOB AND T1.JOBTYPE<>'M'
GROUP BY T1.ETL_SYSTEM,T1.ETL_JOB,T1.DESCRITTION,T1.LAST_JOBSTATU,T1.JOBTYPE
ORDER BY MIN(T2.LEVEL)
;
0 0
- DB2中几个递归语句的案例
- db2 with递归语句
- DB2 With递归语句
- DB2中with递归的妙用
- db2中关于递归(with)的使用
- DB2中substr()语句
- python中递归的两个小案例
- oracle子递归查询语句的几个有用的属性
- oracle子递归查询语句的几个有用的属性
- DB2中更新执行计划的几个常见的方法
- db2中关于递归(with)的使用--合并行列
- DB2在dbvisualizer 客户端执行begin end 语句块的案例,亲测可用
- 完整的DB2 REORG案例
- Flash中常用的几个JS语句
- PHP中常用的几个 mysql 语句
- 使用DB2的with as 语句 实现树的递归查询
- DB2语句中with ur
- db2中删除重复记录的sql语句
- 组播,单播,广播,多播,泛洪的基本概念
- 利用DOS命令编译源文件
- caffe cuda 程序分析
- 分布式服务框架 Zookeeper -- 管理分布式环境中的数据
- 不带源码的发布方式(转自X5论坛)
- DB2中几个递归语句的案例
- vim命令
- Mybatis的输入映射
- 《站在两个世界边缘》-程浩
- 数组类型 普通参数
- php 常用函数
- php final关键字
- UVA 11090 Going in Cycle!!
- @property