데이터 베이스
쿼리문 분석하기
멋진 개구리
2021. 1. 21. 16:31
반응형
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 같은거..*/
반응형