쿼리 먼저 올려드립니다.
WITH RECURSIVE HUN(
cycl, proc_code, proc_name, prnt_proc_code, LEVEL, PATH, CYCLE) AS
(
SELECT A.cycl, A.proc_code, A.proc_name, A.prnt_proc_code, 0, ARRAY[A.proc_code], false
FROM yccics.IC_PROCESS_TBL A
WHERE A.CYCL = '200501'
AND A.LAST_GUBN = 'N'
AND A.PROC_CODE = 'P000001'
UNION ALL
SELECT A.cycl, A.proc_code, A.proc_name, A.prnt_proc_code, LEVEL + 1, PATH||A.proc_code, A.proc_code=ANY(PATH)
FROM yccics.IC_PROCESS_TBL A, HUN B
WHERE A.PRNT_PROC_CODE = B.PROC_CODE
AND A.CYCL = B.CYCL
AND NOT CYCLE
)
SELECT cycl, proc_code, lpad('', 4*LEVEL) || proc_name, prnt_proc_code, LEVEL, PATH
FROM HUN ORDER BY PARH;
특정 table을 with recursive구문으로 트리구조 형태로 조회하려고 하고, 보기 편하게 배열을 추가하려고 하는데
쿼리구문 시작 위에서 부터 4번째 라인에 있는 ARRAY에서 에러가 납니다..
에러 내용은
ERROR: recursive query "hun" column 6 has type character varying(10)[] in non-recursive term but type character varying[] overall
LINE 4: ...l, A.proc_code, A.proc_name, A.prnt_proc_code, 0, ARRAY[A.pr...
^
HINT: Cast the output of the non-recursive term to the correct type.
********** Error **********
ERROR: recursive query "hun" column 6 has type character varying(10)[] in non-recursive term but type character varying[] overall
SQL state: 42804
Hint: Cast the output of the non-recursive term to the correct type.
Character: 160
이렇습니다. 비 재귀용어를 재귀용어로 바꾸라는데 ARRAY를 어떻게 바꾸면 되나요? |