꿈꾸는 개발자, DBA 커뮤니티 구루비

sql 문의드립니다.(오라클 9i)

by 임형섭 [SQL Query] [2017.10.25 16:53:20]

a테이블

SELECT '아래와 같은 문의입니다 기계' text01 FROM dual UNION ALL
SELECT '아래와 같은 문의입니다 도어 조치바랍니다.' text01 FROM dual UNION ALL
SELECT '문의입니다 마루 조치바랍니다.' text01 FROM dual UNION ALL
SELECT '문의입니다 창문' text01 FROM dual UNION ALL
SELECT '문의입니다 책상 조치바랍니다.' text01 FROM dual UNION ALL
SELECT '문의입니다 의자' text01 FROM dual UNION ALL
SELECT '문의입니다' text01 FROM dual

b테이블
SELECT '기계, 도어' text01, '001' code FROM dual UNION ALL
SELECT '책상, 의자' text01, '002' code FROM dual UNION ALL
SELECT '기타' text01, '003' code FROM dual

두개의 테이블이 있습니다.

a테이블의 text01 컬럼에서

b테이블의 text01 컬럼을 조회하여

기계 또는 도어 라는 문구가 있으면 001 코드값을

책상 또는 의자가 있으면 002 값을

그외는 003을 주고 싶습니다.

일일히 따로 sql을 작성하자니 건수가 많다고 해서

가능한한 한번에 조회가 가능한 sql을 만들고자하는데

혹시 어떤방법이 있을까요?

아래는 결과값입니다.

select '아래와 같은 문의입니다 기계' text01, '001' code_re from dual union all
select '아래와 같은 문의입니다 도어 조치바랍니다.'text01, '001' code_re from dual union all
select '문의입니다 마루 조치바랍니다.' text01, '003' code_re from dual union all
select '문의입니다 창문' text01, '003' code_re from dual union all
select '문의입니다 책상 조치바랍니다.' text01, '002' code_re from dual union all
select '문의입니다 의자' text01, '002' code_re from dual union all
select '문의입니다' text01, '003' code_re from dual

by jkson [2017.10.25 16:58:37]

with t1 as
(
select '아래와 같은 문의입니다 기계' text01 from dual union all
select '아래와 같은 문의입니다 도어 조치바랍니다.' text01 from dual union all
select '문의입니다 마루 조치바랍니다.' text01 from dual union all
select '문의입니다 창문' text01 from dual union all
select '문의입니다 책상 조치바랍니다.' text01 from dual union all
select '문의입니다 의자' text01 from dual union all
select '문의입니다' text01 from dual  
)
, t2 as
(
select '기계, 도어' text01, '001' code from dual union all
select '책상, 의자' text01, '002' code from dual union all
select '기타' text01, '003' code from dual
)
select a.text01
     , nvl(to_char(wm_concat(case when regexp_count(a.text01, replace(b.text01, ',', '|')) > 0 
                                  then b.code 
                             end)), '003') fg
  from t1 a, t2 b
 where b.code != '003'
group by a.text01

by 임형섭 [2017.10.25 17:15:57]

오라클이 9i 버젼이다 보니

regexp_count
wm_concat

이 함수를 사용할수가 없네요.

답변감사드립니다.^^

by 우리집아찌 [2017.10.25 17:00:17]

결과값은 어떻게 보여주면되나요?

by 임형섭 [2017.10.25 17:11:40]

이런식으로 보여지면 됩니다

답변감사드립니다. ^^

by jkson [2017.10.25 17:20:47]

아.. 9i군요.

with t1 as
(
select '아래와 같은 문의입니다 기계' text01 from dual union all
select '아래와 같은 문의입니다 도어 조치바랍니다.' text01 from dual union all
select '문의입니다 마루 조치바랍니다.' text01 from dual union all
select '문의입니다 창문' text01 from dual union all
select '문의입니다 책상 조치바랍니다.' text01 from dual union all
select '문의입니다 의자' text01 from dual union all
select '기타도어문의입니다' text01 from dual  
)
, t2 as
(
select '기계,도어' text01, '001' code from dual union all
select '책상,의자' text01, '002' code from dual union all
select '기타' text01, '003' code from dual
)
select a.text01
     , nvl(trim(both ',' from
           substr(xmlagg(xmlelement(x, ','
                                 , (case when regexp_count(a.text01, replace(b.text01, ',', '|')) > 0
                                         then b.code
                                    end))
                  order by b.code).extract('//text()'), 2)), '003') fg
  from t1 a, t2 b
 where b.code != '003'
group by a.text01

일단 결과는 나오는데 제가 뭘 잘못했는지.. 왜 trim이 필요한지.. 왜 그럴까요?;

by 임형섭 [2017.10.25 17:27:42]

버젼이 9i 다보니

regexp_count 이 함수도

사용이 안되는가봅니다.;;;

답변감사드립니다. ^^

by jkson [2017.10.25 17:28:56]

아 그렇네요. 잠시만요ㅋㅋ

by jkson [2017.10.25 17:31:43]

select a.text01
     , nvl(trim(both ',' from
           substr(xmlagg(xmlelement(x, ','
                                 , (case when instr(a.text01, substr(b.text01,1,instr(b.text01,',')-1)) > 0
                                           or instr(a.text01, substr(b.text01,instr(b.text01,',')+1)) > 0
                                         then b.code
                                    end))
                  order by b.code).extract('//text()'), 2)), '003') fg
  from t1 a, t2 b
 where b.code != '003'
group by a.text01

by 임형섭 [2017.10.25 17:35:00]

감사드립니다 ^^ 해당 sql을 분석하여 같은 질문 안드리도록 노력하겠습니다. 꾸벅~^^

by 우리집아찌 [2017.10.25 17:47:19]

WITH A AS (
SELECT '아래와 같은 문의입니다 기계 도어' text01 FROM dual UNION ALL
SELECT '아래와 같은 문의입니다 도어 조치바랍니다.' text01 FROM dual UNION ALL
SELECT '문의입니다 마루 조치바랍니다.' text01 FROM dual UNION ALL
SELECT '문의입니다 창문' text01 FROM dual UNION ALL
SELECT '문의입니다 책상 조치바랍니다.' text01 FROM dual UNION ALL
SELECT '문의입니다 의자' text01 FROM dual UNION ALL
SELECT '문의입니다' text01 FROM dual  
) , B AS (
SELECT '기계,도어' text01, '001' code FROM dual UNION ALL
SELECT '책상,의자' text01, '002' code FROM dual UNION ALL
SELECT '기타' text01, '003' code FROM dual  
) , C AS (
   SELECT CODE
       , CASE WHEN LV = 1 THEN SUBSTR(B.TEXT01 , 1 , INSTR(B.TEXT01,',',1,1) - 1)
              WHEN LV = 2 THEN SUBSTR(B.TEXT01 , INSTR(B.TEXT01,',',1,1) + 1  , LENGTH(B.TEXT01))
         END VAL
    FROM B 
       ,( SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 2 ) B
)


SELECT DISTINCT TEXT01 , CODE AS CODE_RE 
  FROM A LEFT OUTER JOIN C
    ON A.TEXT01 LIKE '%' || C.VAL || '%'
   AND C.VAL IS NOT NULL

by 임형섭 [2017.10.25 18:00:13]

답변감사드립니다 ^^

해당sql을 분석하여 같은 질문 안드리도록 노력하겠습니다. ^^

by 마농 [2017.10.25 18:27:50]

1. 코드값은 두개 묶음만 있는지?
- 혹시 3개 이상 묶음은 없는지?
- 예) '사과, 배, 귤, 수박'
2. 만족하는 코드가 두가지 이상 겹치는 경우엔?
- 어떤 결과가 나와야 할까요?
- 예) '의자 딸린 기계'

by 고수가되고싶어요 [2017.12.08 17:03:05]

WITH a AS (
SELECT '아래와 같은 문의입니다 기계' text01 FROM dual UNION ALL
SELECT '아래와 같은 문의입니다 도어 조치바랍니다.' text01 FROM dual UNION ALL
SELECT '문의입니다 마루 조치바랍니다.' text01 FROM dual UNION ALL
SELECT '문의입니다 창문' text01 FROM dual UNION ALL
SELECT '문의입니다 책상 조치바랍니다.' text01 FROM dual UNION ALL
SELECT '문의입니다 의자' text01 FROM dual UNION ALL
SELECT '문의입니다' text01 FROM dual  
), b AS(

SELECT '기계, 도어' text01, '001' code FROM dual UNION ALL
SELECT '책상, 의자' text01, '002' code FROM dual UNION ALL
SELECT '기타' text01, '003' code FROM dual  
)

SELECT a1.text01
      ,NVL((SELECT b1.code
              FROM b b1
            WHERE a1.text01 like '%'||(SUBSTR(b1.text01,1,2))||'%' OR a1.text01 like '%'||NVL((SUBSTR(b1.text01,5)),0)||'%'
                ),'003')  code
FROM a a1

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변