CTE递归修改为start with

 CTE递归修改为start with
一、背景
Gbase 8a 通过修改_t_gcluster_support_cte参数，可以支持cte函数，但是不支持cte递归调用，所以通过将cte递归修改为start with实现相应功能

二、修改方法
示例：
1.原sql
WITH TMP(CORPORATION,OPUN_COD,BCT_REL_CODE,LEVEL) AS (
SELECT a.LEGAL_PERSON_ID, a.OPUN_COD, a.BCT_REL_CODE, 1 FROM TF_IP_BR_BRHREL a WHERE a.BCT_REL_TYP = '14' AND a.BCT_REL_TYP2 = '00' AND a.BCT_REL_CODE != '999999999' AND a.OPUN_COD != '999999999' 
UNION ALL 
SELECT b.LEGAL_PERSON_ID, b.OPUN_COD, a.BCT_REL_CODE, LEVEL + 1 FROM TMP a, TF_IP_BR_BRHREL b WHERE a.OPUN_COD = b.BCT_REL_CODE AND b.BCT_REL_TYP = '14' AND b.BCT_REL_TYP2 = '00' AND a.LEVEL < 5 )
SELECT DISTINCT a.CORPORATION, '#batch_date#', a.OPUN_COD, b.CORPORATION, a.BCT_REL_CODE, a.LEVEL FROM TMP a LEFT JOIN HLC_ORGANIZATION b ON a.BCT_REL_CODE = b.ORG_CD UNION SELECT DISTINCT b.CORPORATION, '#batch_date#', a.BCT_REL_CODE, b.CORPORATION, a.BCT_REL_CODE, 0 FROM TF_IP_BR_BRHREL a LEFT JOIN HLC_ORGANIZATION b ON a.BCT_REL_CODE = b.ORG_CD WHERE a.BCT_REL_TYP = '14' AND a.BCT_REL_TYP2 = '00' AND a.BCT_REL_CODE != '999999999' AND a.OPUN_COD != '999999999'

2.改后sql（只修改了with as递归部分）
SELECT LEGAL_PERSON_ID, OPUN_COD, connect_by_root BCT_REL_CODE "BCT_REL_CODE", level FROM (SELECT * FROM TF_IP_BR_BRHREL a WHERE a.BCT_REL_TYP = '14' AND a.BCT_REL_TYP2 = '00' AND a.BCT_REL_CODE != '999999999' AND a.OPUN_COD != '999999999' ) a start with BCT_REL_CODE in (SELECT distinct BCT_REL_CODE FROM TF_IP_BR_BRHREL a WHERE a.BCT_REL_TYP = '14' AND a.BCT_REL_TYP2 = '00' AND a.BCT_REL_CODE != '999999999' AND a.OPUN_COD != '999999999' )  connect by BCT_REL_CODE= prior OPUN_COD and level<5
1.With as 判断子查父还是父查子（opun_cod为子列，BCT_REL_CODE为父列）
由以下语句判断为父查子，tmp为cte临时表，where条件后为临时表子列=实表父列
FROM TMP a, TF_IP_BR_BRHREL b WHERE a.OPUN_COD = b.BCT_REL_CODE

2.start with判断子查父还是父查子
Prior 后面跟着子列 就往子查询，跟着父列就往父查询，以下语句为父查子
connect by BCT_REL_CODE= prior OPUN_COD and level<5
3.开始条件修改
With as 开始条件
SELECT a.LEGAL_PERSON_ID, a.OPUN_COD, a.BCT_REL_CODE, 1 FROM TF_IP_BR_BRHREL a WHERE a.BCT_REL_TYP = '14' AND a.BCT_REL_TYP2 = '00' AND a.BCT_REL_CODE != '999999999' AND a.OPUN_COD != '999999999' 
Start with 语法修改为，拿出符合条件的条数
FROM (SELECT * FROM TF_IP_BR_BRHREL a WHERE a.BCT_REL_TYP = '14' AND a.BCT_REL_TYP2 = '00' AND a.BCT_REL_CODE != '999999999' AND a.OPUN_COD != '999999999' ) a

4.Start with多根查询
Start with 多根查询可以使用in条件，对应with as 开始条件部分，因为最终是父查子，所以我们要在开始条件中获取所有符合条件的父列。
With as 开始条件，所有满足where条件的列
SELECT a.LEGAL_PERSON_ID, a.OPUN_COD, a.BCT_REL_CODE, 1 FROM TF_IP_BR_BRHREL a WHERE a.BCT_REL_TYP = '14' AND a.BCT_REL_TYP2 = '00' AND a.BCT_REL_CODE != '999999999' AND a.OPUN_COD != '999999999' 
修改为start with 多根查询条件，因为是父查子，start with后跟父列，in 条件后跟所有满足条件的父列
start with  BCT_REL_CODE in (SELECT distinct BCT_REL_CODE FROM TF_IP_BR_BRHREL a WHERE a.BCT_REL_TYP = '14' AND a.BCT_REL_TYP2 = '00' AND a.BCT_REL_CODE != '999999999' AND a.OPUN_COD != '999999999' ) 
5.with as 和start with执行结果差异点
1).With as 父查子，父列值为根父，start with为上一级父节点值
例如：
1--2--3 ，1为2的父节点，2为3的父节点，with as 查询结果父节点值为1，start with父节点为2
解决方法：使用connect_by_root BCT_REL_CODE "BCT_REL_CODE"
2).with as 语句中通过PARENT_ORG_CODE||ORG_CD方式可以获取父节点路径
Start with中可以使用参数SYS_CONNECT_BY_PATH(column,char)
示例2：
:WITH TEMP( ORG_CODE,PARENT_ORG_CODE) AS ( SELECT ORG_CD,VARCHAR(ORG_CD,180) FROM HLC_ORG_RELA_HIS WHERE ORG_RELA_TYPE_CD='00' AND replace(CURRENT DATE,'-','') BETWEEN STARTDATE AND ENDDATE 
UNION ALL SELECT ORG_CD,PARENT_ORG_CODE||ORG_CD FROM TEMP,HLC_ORG_RELA_HIS WHERE ORG_CODE=CHPARENTORG AND ORG_RELA_TYPE_CD='00' AND replace(CURRENT DATE,'-','') BETWEEN STARTDATE AND ENDDATE ) SELECT A.CORPORATION, B.ORG_CODE, SUBSTR(B.PARENT_ORG_CODE,1,9) AS PARENT_ORG_CODE, A.CHORGNAME, A.CHAGGFLAG, A.CHORGLEVEL, A.ORG_BASE_FLAG, A.ORG_OFFSET_FLAG, A.CHPAYREPORTFLAG AS CHREPORTFLAG FROM TEMP B,HLC_ORGANIZATION A WHERE B.ORG_CODE=A.ORG_CD AND replace(CURRENT DATE,'-','') BETWEEN A.CHOPENDATE AND A.CHCLOSEDATE

修改方法：
select connect_by_root  org_cd "org_cd",SYS_CONNECT_BY_PATH(org_cd,'/') from (SELECT * FROM HLC_ORG_RELA_HIS WHERE ORG_RELA_TYPE_CD='00' AND replace(CURRENT_DATE,'-','') BETWEEN STARTDATE AND ENDDATE ) a start with org_cd in(SELECT org_cd FROM HLC_ORG_RELA_HIS WHERE ORG_RELA_TYPE_CD='00' AND replace(CURRENT_DATE,'-','') BETWEEN STARTDATE AND ENDDATE) connect by prior CHPARENTORG=org_cd