혹시 어느 부분이 문제가 되는거실까요??

제목과 같습니다. 오라클에서 사용하던 기능입니다. 그런데 mysql에서도 사용하려고 하니 문제가 발생합니다. 

작업을 해 보니 

 tmp2(no, cnt, rn, usage) AS
(
SELECT no
     , cnt
     , rn
     , REPLACE(usage, feature, value) usage
  FROM tmp1
 WHERE rn = 1
 UNION ALL
SELECT a.no
     , a.cnt
     , b.rn
     , REPLACE(a.usage, b.feature, b.value) usage
  FROM tmp2 a
     , tmp1 b
 WHERE b.rn = a.rn + 1
   AND b.no = a.no
)

에서 tmp2가 본인을 선언한 내부에서 다시 사용되는데 이부분을 어떻게 mysql에서 구현할지 모르겠습니다.

1. 시스템 예약어 사용 불가로 명칭 변경
- 변경전 : usage
- 변경후 : usage_
2. 인라인뷰 별칭 필수
- 변경전 : FROM (...)
- 변경후 : FROM (...) a
3. dual 제거
- 변경전 : FROM dual
- 변경후 : 
4. 함수 변경
- 변경전 : 'mv' || (MOD(dr - 1, 3) + 1)
- 변경후 : CONCAT('mv', ((dr - 1) % 3 + 1))
5. WITH Recursive 사용
- 제일 첫 WITH 문장에 Recursive 가 와야 함
- 따라서 다른 with 문을 Recursive wirh 문 안쪽에 넣어야 함
6. 기존 질문
- http://gurubee.net/article/84651

WITH RECURSIVE tmp2(no, cnt, rn, usage_) AS
(
WITH usage_ AS
(
SELECT 1 no, 'AAEKZ OR AAED3' usage_
UNION ALL SELECT 2, 'AAEKZ OR AAED3'
UNION ALL SELECT 3, 'AAEA7 AND KBE00'
UNION ALL SELECT 4, 'AAEA7 AND KBE00'
UNION ALL SELECT 5, 'AAEA7 AND KBE00'
UNION ALL SELECT 6, 'AAEKZ OR AAEB5 OR AAED2'
UNION ALL SELECT 7, '(AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)'
UNION ALL SELECT 8, '(AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)'
UNION ALL SELECT 9, '(AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)'
)
, model_varaint AS
(
SELECT 'mv1' modelVariant, 'AAEKZ' feature, 'S' value
UNION ALL SELECT 'mv1', 'AAED3', 'O'
UNION ALL SELECT 'mv1', 'AAEA7', 'O'
UNION ALL SELECT 'mv1', 'KBE00', 'S'
UNION ALL SELECT 'mv1', 'KBE01', 'S'
UNION ALL SELECT 'mv1', 'KBE02', '-'
UNION ALL SELECT 'mv1', 'AAEB5', '-'
UNION ALL SELECT 'mv1', 'AAED2', 'S'
UNION ALL SELECT 'mv2', 'AAEKZ', 'S'
UNION ALL SELECT 'mv2', 'AAED3', 'S'
UNION ALL SELECT 'mv2', 'AAEA7', '-'
UNION ALL SELECT 'mv2', 'KBE00', '-'
UNION ALL SELECT 'mv2', 'KBE01', 'S'
UNION ALL SELECT 'mv2', 'KBE02', '-'
UNION ALL SELECT 'mv2', 'AAEB5', '-'
UNION ALL SELECT 'mv2', 'AAED2', 'S'
UNION ALL SELECT 'mv3', 'AAEKZ', 'S'
UNION ALL SELECT 'mv3', 'AAED3', 'S'
UNION ALL SELECT 'mv3', 'AAEA7', 'S'
UNION ALL SELECT 'mv3', 'KBE00', 'S'
UNION ALL SELECT 'mv3', 'KBE01', 'S'
UNION ALL SELECT 'mv3', 'KBE02', 'S'
UNION ALL SELECT 'mv3', 'AAEB5', '-'
UNION ALL SELECT 'mv3', 'AAED2', 'S'
)
, tmp1 AS
(
SELECT no, usage_
     , modelvariant, feature, value
     , COUNT(*) OVER(PARTITION BY no) cnt
     , ROW_NUMBER() OVER(PARTITION BY no ORDER BY feature) rn
  FROM (SELECT a.no, a.usage_
             , b.modelvariant, b.feature, b.value
             , DENSE_RANK() OVER(PARTITION BY b.feature ORDER BY a.no) dr
          FROM usage_ a
             , model_varaint b
         WHERE INSTR(a.usage_, b.feature) > 0
        ) a
 WHERE modelvariant = CONCAT('mv', ((dr - 1) % 3 + 1))
)
SELECT no
     , cnt
     , rn
     , REPLACE(usage_, feature, value) usage_
  FROM tmp1
 WHERE rn = 1
 UNION ALL
SELECT a.no
     , a.cnt
     , b.rn
     , REPLACE(a.usage_, b.feature, b.value) usage_
  FROM tmp2 a
     , tmp1 b
 WHERE b.rn = a.rn + 1
   AND b.no = a.no
)
SELECT no
     , usage_
  FROM tmp2
 WHERE rn = cnt
;

감사합니다. 역시 마농님이 해결해 주시네요.