반응형
SELECT LEVEL LEV, NVL(M.AMA_IDX, 0) AMA_IDX, A.AMI_IDX, A.UP_AMI_IDX, NVL(M.MEM_IDX, 1) MEM_IDX, A.MENU_NAME, A.MODE_NAME ,
CASE WHEN A.VALUE_NAME IS NULL THEN (SELECT VALUE_NAME FROM ADMIN_MENU_INFO WHERE UP_AMI_IDX=A.AMI_IDX AND SORT=1 AND DEL_YN='N' AND VIEW_YN='Y')
ELSE A.VALUE_NAME END VALUE_NAME, A.SORT , NVL(A.VIEW_YN, 'N') VIEW_YN, NVL(A.TAB_YN, 'N') TAB_YN , NVL(M.AUTH_L_YN, 'N') AUTH_L_YN, NVL(M.AUTH_W_YN, 'N') AUTH_W_YN, NVL(M.AUTH_M_YN, 'N') AUTH_M_YN , NVL(M.AUTH_D_YN, 'N') AUTH_D_YN, NVL(M.AUTH_R_YN, 'N') AUTH_R_YN, NVL(M.AUTH_P_YN, 'N') AUTH_P_YN
FROM GIMHAE.ADMIN_MENU_INFO A
LEFT JOIN (SELECT * FROM ADMIN_MENU_AUTH WHERE MEM_IDX=1 AND SITE_IDX = 1 ORDER BY AMI_IDX ASC) M
ON A.AMI_IDX=M.AMI_IDX WHERE 1=1 START WITH A.UP_AMI_IDX = 1 CONNECT BY PRIOR A.AMI_IDX = A.UP_AMI_IDX ORDER SIBLINGS BY A.UP_AMI_IDX ASC, SORT ASC;
함수 : NVL("값", "지정값")
CASE 조건 WHEN 값 THEN
LEVEL START WITH A.UP_AMI_IDX =1 /*1부터 시작점/ CONNECTION BY PRIOR A.AMI_IDX = A.UP_AMI_IDX /*PRIOR는 상위행을 참조하라 라는 뜻 상위행 A.AMI_IDX = A.UP_AMI_IDX 같은거..*/
반응형
'데이터 베이스' 카테고리의 다른 글
[mysql] 컬럼끼리 값 바꾸기. (0) | 2023.06.27 |
---|---|
db 덤프 - oracle (0) | 2021.05.13 |
mysql row가 여러개인 update 문 (0) | 2021.04.01 |
outer join (0) | 2021.01.15 |
댓글