테이블 설계가 궁금하신가요?

아니면 이미 설계는 되어 있는데 쿼리가 궁금하신가요?

CREATE TABLE BOM (

 

PLANT VARCHAR2(4) NOT NULL, -- 사업장

 

FUCTIONLOCID VARCHAR2(20) NOT NULL, -- 기능ID

 

 

FLOORTYPE VARCHAR2(1), -- 층구분

 

FACTORYTYPE VARCHAR2(3), -- 공장구분

 

FUCTIONLOCNAMEKO VARCHAR2(20) , -- 기능명

 

 

PRIMARY KEY (PLANT,FUCTIONLOCID)

 

);

 

 

INSERT INTO BOM VALUES ( 1500, 0000000001, '1', 'F10','1공장1층');

 

INSERT INTO BOM VALUES ( 1500, 0000000002, '1', 'F20','1공장2층');

INSERT INTO BOM VALUES ( 1500, 0000000005, '1', 'F20','1공장2층');

 

INSERT INTO BOM VALUES ( 1500, 0000000003, '2', 'F10','2공장1층');

 

INSERT INTO BOM VALUES( 1500, 0000000004, '2', 'F20','2공장2층');

 

 

 

일단 이런식으로 테이블 구성이 되어있습니다.

 

이 데이터로 tree 구조로 만들려고 하는데

 

사업장

 

ㄴ F10(1공장)

 

ㄴ 1(1층)

 

ㄴ 0000000001(1공장 1층)

 

ㄴ 1(2층)

 

ㄴ 0000000002(1공장 2층)

 

ㄴ 0000000005(1공장 2층)

 

ㄴ F20(2공장)

 

ㄴ2(1층)

 

ㄴ 0000000003(2공장 1층)

 

 

 

 

 

이런 구조의 트리를 만드려고 하는데...

 

 

 

--------------------------------------------------------

 

SELECT LEVEL AS DEPTH

,GUBUN

,LPAD(' ', 2 * (LEVEL - 1)) || A.MENUNAME AS MENUNAME

,PLANT

,FUCTIONLOCID

,EQUIPMENTID

,FACTORYTYPE

FROM (SELECT 1 AS MENUNO,

'' AS GUBUN,

0 AS PARENTNO,

'기능위치 정보' AS MENUNAME,

'' AS PLANT,

'' AS FUCTIONLOCID,

'' AS EQUIPMENTID,

'' AS FACTORYTYPE

FROM DUAL

UNION

SELECT 3,

'C' AS GUBUN,

1,

(A.FACTORYTYPE || '(' || B.CODENAMEKO || ')' ) AS EQUIPTYPE,

PLANT,

'' AS FUCTIONLOCID,

FUCTIONLOCID AS EQUIPMENTID,

FACTORYTYPE

FROM BOM A, 공통코드 B

WHERE FACTORYTYPE = B.CODEID

AND B.CODECLASSID = 'FCTYPE'

-- AND A.PLANT = '1500'

UNION

SELECT 6,

'C-1' AS GUBUN,

3,

(A.FLOORTYPE || '(' || B.CODENAMEKO || ')' ) AS EQUIPTYPE,

PLANT,

'' AS FUCTIONLOCID,

FUCTIONLOCID AS EQUIPMENTID,

FACTORYTYPE

FROM BOM A, 공통코드 B

WHERE A.FLOORTYPE = B.CODEID

AND B.CODECLASSID = 'FLTYPE'

-- AND A.PLANT = '1500'

/*UNION

SELECT 8,

'C-2' AS GUBUN,

6,

(A.FUCTIONLOCID || '(' || A.FUCTIONLOCNAMEKO || ')' ) AS EQUIPTYPE,

PLANT,

'' AS FUCTIONLOCID,

FUCTIONLOCID AS EQUIPMENTID,

FACTORYTYPE

FROM BOM A

WHERE 1=1

AND A.PLANT = '1500'*/

) A

START WITH A.MENUNO = '1'

CONNECT BY PRIOR A.MENUNO = A.PARENTNO

 

AND PLANT = '1500'

;

 

---------------------------------------------------------------------------

 

 

 

현재 이런식으로 짜고 있습니다..

 

그런데 데이터가 각각의 공장,층 마다 모든 데이터가 나옵니다.

 

공장의 층에 맞는 데이터만 나오게 하고 싶습니다..

 

감사합니다~

 

 일단 depth를 식별할수 있는게 없는거 같아요..

제품 bom으로 예를 들면..

상위파트 현재파트 소요량

의 으로 해서 쫓아갈수 있어야할듯한데요..

 

예) 상위 하위파트 개념으로만 보세요..

한국 공장1

공장1 1층

공장1 2층

1층 101호

1층 102 호

102호 총무팀

102호 영업팀

 

저런 형태로 데이터를 구성하고

쫓아가기만 하면되죠..

후아.... 잘 안되네요 ㅠㅠ

with
bom(plant, fuctionlocid, floortype, factorytype, fuctionlocnameko)
as (      select 1500, '0000000001', '1', 'F10','1공장1층' from dual
union all select 1500, '0000000002', '1', 'F20','1공장2층' from dual
union all select 1500, '0000000005', '1', 'F20','1공장2층' from dual
union all select 1500, '0000000003', '2', 'F10','2공장1층' from dual
union all select 1500, '0000000004', '2', 'F20','2공장2층' from dual
)
select substr(lpad(' ',((level-1) * 4),' ')||'┗ '||fuctionlocnameko,2)
  from( select fuctionlocnameko,plant||floortype||factorytype newid
             , case when floortype is null then null
                    when factorytype is null then to_char(plant) else plant || floortype end parentid 
          from (select distinct plant, decode(r,1,null,floortype) floortype, decode(r,1,null,2,null,factorytype) factorytype
                     , decode(r,1,'사업장',2,substrb(fuctionlocnameko,1,5),3,substrb(fuctionlocnameko,6)) fuctionlocnameko
                  from bom,(select level r from dual connect by level <= 3)
               ) comm
         union all
        select fuctionlocid || '('||fuctionlocnameko||')', to_char(fuctionlocid) newid, plant || floortype || factorytype parentid
          from bom
      )
 start with parentid is null
connect by prior newid = parentid
 

잘 응용해 보세요

그냥 단순한 Sample 형태로 만들어봤습니다.

Oracle 은 안써봐서 모르구요.

MSSQL 로 작성되었습니다.

 @part 변수에 검색하고자 하는 값을 입력하시면

 해당 값의 하위건들이 나오게 됩니다..

참고만 하세요~~

 

--쿼리시작

Declare @part  As char(30)
Set @part = '1공장'

With BOM_law (upart,npart,seqx) As
(
     Select NULL   ,'한국'   ,1 Union All
     Select '한국' ,'1공장'  ,1 Union All
     Select '1공장','1층'    ,1 Union All
     Select '1층'  ,'제품1'  ,1 Union All
     Select '1층'  ,'제품2'  ,2 Union All
     Select '1층'  ,'제품5'  ,3 Union All 
     Select '1공장','2층'    ,2 Union All
     Select '2층'  ,'제품3'  ,1 Union All
     Select '2층'  ,'제품4'  ,2 Union All
     Select '한국' ,'2공장'  ,2
)
,BOM (seq,data,upart,npart,seqx) As
(
     Select
            seq  = 1
           ,data = a.npart
           ,a.upart  As upart
           ,a.npart  As npart
           ,a.seqx
       From BOM_law As a
      Where a.npart = @part
     
  Union All   

     Select
            seq  = seq + 1
           ,data = b.npart
           ,b.upart  As upart
           ,b.npart  As npart
           ,b.seqx
       From BOM As a
                     Inner Join
                                BOM_law b
                                          On data = b.upart
                                         And b.seqx <= a.seqx + 1
)
,BOM_EX As
(
    Select
           a.seq
          ,levl = a.seq - 1
          ,a.data
          ,a.upart
          ,a.npart
          ,a.seqx
          ,(
              Case a.seq
                         When 1  Then '1'
                         When 2  Then '11'
                         When 3  Then '111'
                         When 4  Then '1111'
                         When 5  Then '11111'
                         When 6  Then '111111'
                         When 7  Then '1111111'
                         When 8  Then '11111111'
                         When 9  Then '111111111'
                         When 10 Then '1111111111'
              End
           )
             + Convert(
                        char(1)
                       ,
                        (
                          Select
                                 b.seqx  
                            From BOM As b
                           Where b.npart = a.upart
                             And b.seq = a.seq - 1
                        )
                      )
             + CONVERT(char(1),a.seqx)  As serl
      From BOM As a
)     
    Select
           *
      From BOM_EX As a
  Order By
           a.serl Asc
 

--쿼리끝