bom에 상위 품목 조회 0 3 126

by 한만정도경영 [SQL Query] [2018.12.04 18:49:44]


안녕하세요

   아래 Bom 테이블을 이용하여

   a원자재를 검색하면은  상위 모품목, 차상위 모품목,차차상위 모품목을 조회하려고 합니다.

    어떻게 해야 할까요?  문의 드립니다.

꾸벅.

 

 

by 시도르 [2018.12.05 14:43:00]

원자재 조건을 직접 넣긴 했는데, 별도 구분조건으로 변경해주시면 됩니다.

WITH W_BOM AS (
SELECT 'AA' AS parent_item ,'a'  AS child_item FROM dual  UNION ALL
SELECT 'BB' AS parent_item ,'b'  AS child_item FROM dual  UNION ALL
SELECT 'CC' AS parent_item ,'c'  AS child_item FROM dual  UNION ALL
SELECT 'AAA' AS parent_item ,'AA'  AS child_item FROM dual  UNION ALL
SELECT 'AAA' AS parent_item ,'BB'  AS child_item FROM dual  UNION ALL
SELECT 'AAB' AS parent_item ,'CC'  AS child_item FROM dual  UNION ALL
SELECT 'ABCD' AS parent_item ,'AAA'  AS child_item FROM dual
UNION ALL SELECT 'ABCD' AS parent_item ,'AAB'  AS child_item FROM dual
)

SELECT root_item
      ,MAX(p_item1) AS  p_item1
      ,MAX(p_item2) AS  p_item2
      ,MAX(p_item3) AS  p_item3
FROM  (
      SELECT connect_by_root child_item AS root_item
            ,CASE WHEN LEVEL = 1 THEN parent_item END  AS p_item1
            ,CASE WHEN LEVEL = 2 THEN parent_item END  AS p_item2
            ,CASE WHEN LEVEL = 3 THEN parent_item END  AS p_item3
      FROM  w_bom w
      START WITH child_item IN ('a' ,'b' ,'c') --원자재 구분 조건
      CONNECT BY PRIOR w.parent_item = w.child_item
      )
GROUP BY root_item 

 


by 한만정도경영 [2018.12.05 17:23:52]

댓글 등록 감사합니다.

근데.   확인해 보니,  원자재 하나에  복수의 반제품 이 있을 경우  MAX함수 때문에  상위 1개  밖에 표기가 안되는데 MAX(p_item1)

혹시  다수의   상위 품목 표기 구현은 안되는 건가요?

 


by 마농 [2018.12.06 08:23:17]
WITH t AS
(
SELECT 'AA' p_item, 'a' item FROM dual
UNION ALL SELECT 'BB'  , 'b'   FROM dual
UNION ALL SELECT 'CC'  , 'a'   FROM dual
UNION ALL SELECT 'AAA' , 'AA'  FROM dual
UNION ALL SELECT 'AAA' , 'BB'  FROM dual
UNION ALL SELECT 'AAB' , 'CC'  FROM dual
UNION ALL SELECT 'ABCD', 'AAA' FROM dual
UNION ALL SELECT 'ABCD', 'AAB' FROM dual
)
SELECT item
     , REGEXP_SUBSTR(p, '[^-]+', 1, 1) item_1
     , REGEXP_SUBSTR(p, '[^-]+', 1, 2) item_2
     , REGEXP_SUBSTR(p, '[^-]+', 1, 3) item_3
  FROM (SELECT CONNECT_BY_ROOT(item) item
             , SYS_CONNECT_BY_PATH(p_item, '-') p
          FROM t
         WHERE CONNECT_BY_ISLEAF = 1
         START WITH item = 'a'
         CONNECT BY PRIOR p_item = item
           AND LEVEL <= 3
        )
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입