AT1 AS
SELECT /*+ OPT_PARAM('_optimizer_connect_by_cost_based' 'false') */
A.PLAN_ID,
DECODE(A.PLAN_TYPE,'0','年初计划','1','中期调整',A.PLAN_TYPE) PLAN_TYPE,
A.PLAN_YEAR,
A.CATEGORY_ID,
C.FIRST_NAME,
.................................
FROM PL_PLAN_BASE A
left JOIN
(SELECT B.CATEGORY_ID,
REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( B.CLASS_NAME,'/'),'[^/]+',1,1,'i') FIRST_NAME,
REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( B.CLASS_NAME,'/'),'[^/]+',1,2,'i') SECOND_NAME,
B.CLASS_NAME THIRD_NAME
FROM PC_CATEGORY_BASE B
START WITH B.PARENT_ID IS NULL
CONNECT BY B.PARENT_ID=PRIOR B.CATEGORY_ID ) C ON A.CATEGORY_ID=C.CATEGORY_ID
LEFT JOIN
(SELECT DEPT_NAME,DEPT_CODE FROM BI_DEPT ) D ON D.DEPT_CODE=A.APPLY_DEPT_CODE
LEFT JOIN
(SELECT T.CATEGORY_ID,D.DEPT_NAME,T.ORG_CODE
FROM PC_CATEGORY_ORG T,BI_DEPT D
WHERE T.CENTRALIZED_DEP_CODE=D.DEPT_CODE
)E ON (E.CATEGORY_ID =a.Category_Id AND E.ORG_CODE=A.CRT_ORG_CODE)
where a.data_state='0'
and a.plan_org_name not like '%测试%'
and a.plan_material_name not like '%测试%'
and a.crt_org_name not like '%null%';
再次查询就不会报错:可以查出来。
这边是针对该版本,后面的版本ORACLE 是修复了