WITH t03 AS ( SELECT '1500' plant, '001' fId, '설비1' flNm, 'f10' fcType, '1' flType FROM dual union all SELECT '1500' plant, '002' fId, '설비2' flNm, 'f10' fcType, '2' flType FROM dual union all SELECT '1500' plant, '003' fId, '설비3' flNm, 'f20' fcType, '1' flType FROM dual union all SELECT '1500' plant, '004' fId, '설비4' flNm, 'f20' fcType, '2' flType FROM dual union all SELECT '1500' plant, '005' fId, '설비5' flNm, 'f30' fcType, '1' flType FROM dual ) WITH t01 AS ( SELECT '1500' plant, '001' fId, 'AAA-0001' eqId, '10' eqType FROM dual union all SELECT '1500' plant, '002' fId, 'AAA-0002' eqId, '20' eqType FROM dual union all SELECT '1500' plant, '003' fId, 'AAA-0003' eqId, '10' eqType FROM dual union all SELECT '1500' plant, '004' fId, 'AAA-0004' eqId, '10' eqType FROM dual union all SELECT '1500' plant, '005' fId, 'AAA-0005' eqId, '20' eqType FROM dual union all SELECT '1500' plant, '006' fId, 'AAA-0006' eqId, '20' eqType FROM dual union all SELECT '1500' plant, '007' fId, 'AAA-0007' eqId, '10' eqType FROM dual ) WITH t06 AS ( SELECT '1500' plant,'1' levelno , '1' seq , '10' unit , '' part , '' equip FROM DUAL UNION ALL SELECT '1500' plant,'1' levelno , '2' seq , '20' unit , '' part , '' equip FROM DUAL UNION ALL SELECT '1500' plant,'2' levelno , '1' seq , '10' unit , 'A' part , '' equip FROM DUAL UNION ALL SELECT '1500' plant,'2' levelno , '2' seq , '20' unit , 'B' part , '' equip FROM DUAL UNION ALL SELECT '1500' plant,'3' levelno , '1' seq , '10' unit , 'A' part , '001-1' equip FROM DUAL UNION ALL SELECT '1500' plant,'3' levelno , '2' seq , '10' unit , 'A' part , '002-1' equip FROM DUAL )
테이블 3개를 조인해서 하나의 tree 구조로 만들려고 합니다.
자꾸 질문드려 죄송하네요..
t03.fcType
ㄴ t03.flType
ㄴ t03.fId || t03.flNm
ㄴ t01.eqId
ㄴ t06.unit
ㄴ t06.part
ㄴ t06.equip
이런 계층 구조의 데이터를 뽑아 내려고 합니다.
조인 조건은 각 테이블의 같은 컬럼 값이 있습니다.
예제를 찾으려고 했는데 잘 안보이는것 같네요...
참고가 될진 모르겠지만.. to6 의 구조는 아래와 같이 표현합니다.
SELECT LEVELNO , SEQ , UNIT , PART , EQUIP FROM ( SELECT LEVELNO , SEQ , UNIT , PART , EQUIP , CASE WHEN PART IS NULL THEN '1' ELSE PART END PART_ORDER_BY FROM T ORDER BY SEQ , UNIT , PART_ORDER_BY )
지난번에 마농님 쿼리 컨닝해서 만들었는데..
맞는건지는 모르겠네요..
그리고 T06 테이블에 NULL 값들이 있어서 확인이 안되서 일단 임의값을 넣었습니다.
업무 내용을 몰라 어떻게 해야 할지??
WITH t03 AS ( SELECT '1500' plant, '001' fId, '설비1' flNm, 'f10' fcType, '1' flType FROM dual union all SELECT '1500' plant, '002' fId, '설비2' flNm, 'f10' fcType, '2' flType FROM dual union all SELECT '1500' plant, '003' fId, '설비3' flNm, 'f20' fcType, '1' flType FROM dual union all SELECT '1500' plant, '004' fId, '설비4' flNm, 'f20' fcType, '2' flType FROM dual union all SELECT '1500' plant, '005' fId, '설비5' flNm, 'f30' fcType, '1' flType FROM dual ) , t01 AS ( SELECT '1500' plant, '001' fId, 'AAA-0001' eqId, '10' eqType FROM dual union all SELECT '1500' plant, '002' fId, 'AAA-0002' eqId, '20' eqType FROM dual union all SELECT '1500' plant, '003' fId, 'AAA-0003' eqId, '10' eqType FROM dual union all SELECT '1500' plant, '004' fId, 'AAA-0004' eqId, '10' eqType FROM dual union all SELECT '1500' plant, '005' fId, 'AAA-0005' eqId, '20' eqType FROM dual union all SELECT '1500' plant, '006' fId, 'AAA-0006' eqId, '20' eqType FROM dual union all SELECT '1500' plant, '007' fId, 'AAA-0007' eqId, '10' eqType FROM dual ) , t06 AS ( SELECT '1500' plant,'1' levelno , '1' seq , '10' unit , 'A1' part , '01' equip FROM DUAL UNION ALL SELECT '1500' plant,'1' levelno , '2' seq , '20' unit , 'A2' part , '02' equip FROM DUAL UNION ALL SELECT '1500' plant,'2' levelno , '1' seq , '10' unit , 'A' part , '03' equip FROM DUAL UNION ALL SELECT '1500' plant,'2' levelno , '2' seq , '20' unit , 'B' part , '04' equip FROM DUAL UNION ALL SELECT '1500' plant,'3' levelno , '1' seq , '10' unit , 'A' part , '001-1' equip FROM DUAL UNION ALL SELECT '1500' plant,'3' levelno , '2' seq , '10' unit , 'A' part , '002-1' equip FROM DUAL ) SELECT DECODE(GROUPING_ID(t03.fcType, t03.flType, t03.fId, t01.eqId, t06.unit, t06.part, t06.equip) ,63,t03.fcType ,31,' ㄴ '||t03.flType ,15,' ㄴ '||t03.fId || t03.flNm ,7,' ㄴ '||t01.eqId ,3,' ㄴ '||t06.unit ,1,' ㄴ '||t06.part ,0,' ㄴ '||t06.equip ) TREE FROM T03, T01, T06 WHERE T03.PLANT = T01.PLANT AND T03.FID = T01.FID AND T03.PLANT = T06.PLANT GROUP BY t03.fcType, ROLLUP(t03.flType, (t03.fId , t03.flNm), t01.eqId, t06.unit, t06.part, t06.equip) ORDER BY t03.fcType , t03.flType NULLS FIRST , t03.fId NULLS FIRST , t01.eqId NULLS FIRST , t06.unit NULLS FIRST , t06.part NULLS FIRST , t06.equip NULLS FIRST ;
SELECT DECODE(GROUPING_ID(t03.fcType, t03.flType, t03.fId, t01.eqId, t06.unit, t06.part, t06.equip) ,63,t03.fcType ,31,' ㄴ '||t03.flType ,15,' ㄴ '||t03.fId || t03.flNm ,7,' ㄴ '||t01.eqId ,3,' ㄴ '||t06.unit ,1,' ㄴ '||t06.part ,0,' ㄴ '||t06.equip ) TREE FROM T03, T01, T06 WHERE T03.PLANT = T01.PLANT AND T03.FID = T01.FID AND T03.PLANT = T06.PLANT AND T06.unit IS NOT NULL AND T06.part IS NOT NULL AND T06.equip IS NOT NULL GROUP BY t03.fcType, ROLLUP(t03.flType, (t03.fId , t03.flNm), t01.eqId, t06.unit, t06.part, t06.equip) ORDER BY t03.fcType , t03.flType NULLS FIRST , t03.fId NULLS FIRST , t01.eqId NULLS FIRST , t06.unit NULLS FIRST , t06.part NULLS FIRST , t06.equip NULLS FIRST ;