데이터베이스 질문과 답변
안녕하세요
oracle -> mysql 쿼리변환 공부진행중에
NVL(SUBSTR(REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(MENU_ID, '/'),'/[[:alnum:]]{9}',1, 2),2), CONNECT_BY_ROOT MENU_ID) AS LV2_MENU_ID
, NVL(REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(MENU_NM, '\'), '[^\]+',1, 2), CONNECT_BY_ROOT MENU_NM ||'[1레벨]') AS LV2_MENU_NM
여기부분은 MYSQL
COALESCE(SUBSTR((REGEXP_SUBSTR(CONCAT(LV2_MENU_ID, '/'), '/[A-Za-zㄱ- |가-힣-0-9]{9}'),1,2),2), MENU_ID) AS LV2_MENU_ID,
COALESCE(REGEXP_SUBSTR(CONCAT(LV2_MENU_NM,'\'),'[^\]+',1,2),CONCAT(MENU_NM, '[root]')) as lv2_menu_nm
mysql 밑에 구문에서 오류가 발생하는데 혹시
쉼표 관련해서 오류나는지 ''\'','\\\' 이런식으로 해도 자꾸 오류가나옵니다
menu_nm, menu_id sys connect path 는 cast ,cocnat으로 구현한 값이
lv2_menu_nm,lv2_menu_id 입니다.
어디부분이 오류인지 궁금해서 질문드립니다.
'
1. CONCAT 이 필요한가요?
- 이미 경로가 구현된 형태인 값에 대해서
- CONCAT 을 다시 할 필요는 없어 보이구요.
2. MySQL 에서의 REGEXP_SUBSTR 은
- 입력 인자가 2개 뿐이라 뒤에 (1,2) 부분이 불가합니다.
- SUBSTRING_INDEX 함수를 이용해 보세요.
- SUBSTRING_INDEX(SUBSTRING_INDEX(lv2_menu_id, '/', 2), '/', -1)
3. ID 의 경우 9자리를 가져오는데?
- 9자리 고정 자리수라면? 그냥 단순하게 SUBSTR 해도 되는 부분입니다.
- SUBSTR(lv2_menu_id, 10, 9)
안녕하세요 언제나 친절한 답변감사합니다.
마농님 말씀을 토대로 변경했는데
oracle
SELECT MENU_ID
, MENU_GRP_DIV_CD
, MENU_NM
, DECODE(MENU_LVL, 1, 'Y', 'N') AS LV1_FLAG
, CONNECT_BY_ROOT MENU_ID AS LV1_MENU_ID
, CONNECT_BY_ROOT MENU_NM AS LV1_MENU_NM
, NVL(SUBSTR(REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(MENU_ID, '/'),'/[[:alnum:]]{9}',1, 2),2), CONNECT_BY_ROOT MENU_ID) AS LV2_MENU_ID
, NVL(REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(MENU_NM, '\'), '[^\]+',1, 2), CONNECT_BY_ROOT MENU_NM ||'root') AS LV2_MENU_NM
FROM CCO_MENU_BAS
START WITH MENU_LVL = 1
CONNECT BY PRIOR MENU_ID = UPR_MENU_ID
ORDER SIBLINGS BY SORT_TURN
mysql
WITH RECURSIVE MENU( MENU_ID, MENU_GRP_DIV_CD, MENU_NM, LV1_FLAG, LV1_MENU_ID, LV1_MENU_NM, LV2_MENU_ID, LV2_MENU_NM, LEVEL, SORT_TURN ) AS(SELECT 1 AS LEVEL, A.MENU_ID, A.MENU_GRP_DIV_CD, A.MENU_NM, (CASE WHEN MENU_LVL = 1 THEN 'Y' ELSE 'N' END) AS LV1_FLAG, A.MENU_ID AS LV1_MENU_ID, A.MENU_NM AS LV1_MENU_NM, cast(MENU_ID as char(100)) AS LV2_MENU_ID, cast(MENU_NM as char(100)) AS LV2_MENU_NM, A.SORT_TURN FROM CCO_MENU_BAS AS A WHERE MENU_LVL = 1 UNION ALL SELECT MENU.LEVEL + 1 AS level, B.MENU_ID, B.MENU_GRP_DIV_CD, B.MENU_NM, (CASE WHEN MENU_LVL = 1 THEN 'Y' ELSE 'N' END) AS LV1_FLAG, MENU.LV1_MENU_ID, MENU.LV1_MENU_NM, concat(LV2_MENU_ID,' / ', B.MENU_ID) AS LV2_MENU_ID, concat(LV2_MENU_NM,'\\', B.MENU_NM) AS LV2_MENU_NM, MENU.SORT_TURN FROM MENU inner join CCO_MENU_BAS AS B ON MENU.MENU_ID = B.UPR_MENU_ID) SELECT MENU_ID, MENU_GRP_DIV_CD, MENU_NM, LV1_FLAG, LV1_MENU_ID, LV1_MENU_NM, COALESCE(substring_index(substring_index(LV2_MENU_ID,'/',2),'/',-1), MENU_ID) AS LV2_MENU_ID, COALESCE(substring_index(LV2_MENU_NM,'[^\]+', 1),concat(MENU_NM, '[root]')) AS LV2_MENU_NM FROM MENU ORDER BY SORT_TURN, MENU_GRP_DIV_CD, LEVEL, MENU_ID
이게 전체쿼리인데 여기서 말씀한 부분 바꾼후 확인해볼려고 하니
Truncated incorrect DOUBLE value (lv1_ id 관련해서) 떠서 2시간넘게 제자리걸음이네요...
제가원하는 것은 lv1 에 id 젤 상위인 출력하고 lv2 에는 그다음걸 출력하는식으로 목적인데
trucate 오류 찾아봐도 해결이안되네요 제가 구현식을 잘못썻을까요?
WITH RECURSIVE menu AS
(
SELECT menu_id
, menu_grp_div_cd
, menu_nm
, 'Y' lv1_flag
, menu_id lv1_menu_id
, menu_nm lv1_menu_nm
, menu_id lv2_menu_id
, menu_nm ||'root' lv2_menu_nm
, CAST(LPAD(sort_turn, 3, '0') AS VARCHAR(99)) sort_turn
FROM cco_menu_bas
WHERE menu_lvl = 1
UNION ALL
SELECT c.menu_id
, c.menu_grp_div_cd
, c.menu_nm
, 'N' lv1_flag
, p.lv1_menu_id
, p.lv1_menu_nm
, CASE WHEN c.menu_lvl THEN 2 THEN c.menu_id ELSE p.lv2_menu_id END lv2_menu_id
, CASE WHEN c.menu_lvl THEN 2 THEN c.menu_nm ELSE p.lv2_menu_nm END lv2_menu_nm
, CONCAT(p.sort_turn, LPAD(c.sort_turn, 3, '0')) sort_turn
FROM menu p
INNER JOIN cco_menu_bas c
ON p.menu_id = c.upr_menu_id
)
SELECT *
FROM menu
ORDER BY sort_turn
;
1. menu_grp_div_cd 의 역할이 뭔가요?
- 메뉴보다 상위개념인가요? 대부류 느낌?
- 그렇다면 이 컬럼도 원본 쿼리의 CONNECT BY 조건에 추가 되어야 하는 조건입니다.
2. sort_turn 은 어떤 값이 어떤 형태로 들어가 있나요?
- 숫자인가요?
- 최대 몇자리?
3. 어차피 1,2 레벨만 보고 싶은것 같은데?
- 굳이 복잡하게 모든 경로 다 연결했다가, 다시 2레벨만 추출할 필요 있나요?
- 처음부터 2레벨일때만 값을 지정하면 되는데요?
4. 이상한 부분
- 정규식때 쓴 걸 그대로 쓰셨네요? '[^\]+', 수정해야 할 것 같은데요.
5. 오류메세지에 나오는 (lv1_ id)는 안보이는데요?
- 오류 나는 쿼리 맞는지?
수정해주신 코드통해 오류 잡았습니다. 정렬 부분
오라클값과 값차이가 나서 이부분은 제가 더 열심히 공부해서
한번 해결해 보겠습니다. 감사합니다.
1. 그룹코드로서 제일 상위 부류는 아니지만 오라클에선
002라고 예를들면 쭈욱 002 있는값이 결과출력되고 003 나옵니다.
sort_turn 앞의 부분에 해당하는값인거같습니다.
2.001002~ 이런식의 최대 12자리 값으로 들어가있습니다.
3. 값은 정렬된 방식으로 lv1 부모노드 lv2 자식 순서대로
lv2에 2순위 3순위 4순위 표현으로 구성된걸 변환하려고 노력중입니다.
lv2에 path 라고 예를들면 lv1-lv2 1-2(여러값) ,1-3,1-4 ,1-5 입니다.
4. 정규식 부분은 추천해주신 방법으로 해봤지만 5번오류가 나와서 확인은 값의 확인은 못했습니다.
저방식말고 '\\'도전해봤습니다.
5. 제 코드에서는 오류가 Truncated incorrect DOUBLE value ('aws000000') 이런식으로 계속 발생하였습니다.
예시를 들어 lv1_id 는 제결과값에 포함된 컬럼 네임이였습니다.
늦으시간까지 감사합니다.
1. sort_turn 에 대한 예측 오류
- 저는 1,2,3 이나 001, 002 형태인걸로 예측하여 경로처럼 이어붙이기를 했는데.
- 아마도 이어붙이기 할 필요가 없는 이미 완성된 형태가 아닌가 생각되네요?
- 1레벨 001, 2레벨은 001002 형태?
- 이런 형태라면 그냥 있는 그대로 정렬하면 될 듯 합니다.
2. menu_grp_div_cd 은
- 아마도 그룹 분류가 아닐까? 생각이 되며
- 조건 추가가 필요할 수 있습니다.
3. 예시 자료를 보여주시면 좋겟네요.
- 원본 대비 결과표
WITH RECURSIVE menu AS
(
SELECT menu_id
, menu_grp_div_cd
, menu_nm
, 'Y' lv1_flag
, menu_id lv1_menu_id
, menu_nm lv1_menu_nm
, menu_id lv2_menu_id
, menu_nm ||'root' lv2_menu_nm
, sort_turn
FROM cco_menu_bas
WHERE menu_lvl = 1
UNION ALL
SELECT c.menu_id
, c.menu_grp_div_cd
, c.menu_nm
, 'N' lv1_flag
, p.lv1_menu_id
, p.lv1_menu_nm
, CASE WHEN c.menu_lvl THEN 2 THEN c.menu_id ELSE p.lv2_menu_id END lv2_menu_id
, CASE WHEN c.menu_lvl THEN 2 THEN c.menu_nm ELSE p.lv2_menu_nm END lv2_menu_nm
, c.sort_turn
FROM menu p
INNER JOIN cco_menu_bas c
ON p.menu_id = c.upr_menu_id
AND p.menu_grp_div_cd = c.menu_grp_div_cd -- 조건 추가 필요할 듯
)
SELECT *
FROM menu
ORDER BY menu_grp_div_cd, sort_turn
;
oracle 제대로된 sort_turn은 1, 2, 3 붙어있는게 아닌 단순구조였습니다. 제가 어제 mysql 결과를 말씀드렸네요
죄송합니다. 결과값이 menu_nm에서도 차이가 나서 제가 이것저것 해보고 어느 정도 정돈되면
결과 첨부하겠습니다. 현재는 정리가 안되서 민폐일거같습니다.
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.