[Oracle] REGEXP_SUBSTR =CASE WHEN END + SUBSTR + INSTR

요구사항: 수량과 가격 한번에 표시 ,품목코드 앞자리수 4개별로 데이터 합치기 , 아이템 타입 수량 * 기존수량 

 

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

 

기존쿼리

SELECT ITEM_CODE
,ITEM_HNAME
,ITEM_TYPE
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '01', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP01
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '02', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP02
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '03', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP03
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '04', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP04
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '05', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP05
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '06', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP06
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '07', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP07
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '08', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP08
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '09', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP09
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '10', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP10
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '11', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP11
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '12', DECODE(:as_flag1, '1', TAX_QTY + ADD_QTY, AMOUNT), 0)) AS DSP12
  FROM
(
SELECT B.DELIV_DATE
,B.ITEM_CODE
,C.ITEM_HNAME
,TO_CHAR(C.ITEM_BOX_QTY)||'/'||C.ITEM_HFGOOD_TYPE AS ITEM_TYPE
,DECODE(A.SLIP_GUBUN, '21', -1, '22', -1, '23', -1, '2A', -1, '41', -1, '51', -1, 1) * (B.TAX_QTY + B.BULK_QTY / DECODE(B.ITEM_CODE, 'HDPL0500', 500, 'RBSS0500', 500, 'UCML0001', 500, 'DPFL0001', 500, C.ITEM_BOX_QTY))  AS TAX_QTY
,DECODE(A.SLIP_GUBUN, '21', -1, '22', -1, '23', -1, '2A', -1, '41', -1, '51', -1, 1) * B.ADD_QTY                                                                                         AS ADD_QTY
,DECODE(A.SLIP_GUBUN, '21', -1, '22', -1, '23', -1, '2A', -1, '41', -1, '51', -1, 1) * DECODE(:as_flag2, '1', B.SUPPLY_AMT, B.TAX_AMT)                                                   AS AMOUNT
 FROM DELIVERY_ORDER A
,DELIVERY_ITEM  B
,ITEM_1         C
WHERE A.ORDER_DATE = B.ORDER_DATE
AND A.ORDER_NO   = B.ORDER_NO
AND A.SLIP_GUBUN <> '41'
AND A.SLIP_GUBUN <> '51'
AND B.ITEM_CODE  = C.ITEM_CODE
AND B.DELIV_DATE BETWEEN :as_frdate AND :as_todate
AND B.ITEM_CODE  LIKE    :as_item_code

UNION ALL
SELECT B.DELIV_DATE                               AS SLIP_GUBUN
,B.ITEM_CODE                                      AS ITEM_CODE
,C.ITEM_HNAME                                     AS ITEM_HNAME
,TO_CHAR(C.ITEM_BOX_QTY)||'/'||C.ITEM_HFGOOD_TYPE AS ITEM_TYPE
,B.QTY                                            AS TAX_QTY
,B.EXTRA_QTY                                      AS ADD_QTY
,B.AMOUNT                                         AS AMOUNT
  FROM SALES_ORDER_EXP A
,SALES_ITEM_EXP  B
,ITEM_1         C
 WHERE A.ORDER_DATE = B.ORDER_DATE
AND A.ORDER_NO = B.ORDER_NO
AND A.SLIP_GUBUN = B.SLIP_GUBUN
AND B.ITEM_CODE = C.ITEM_CODE
AND B.DELIV_DATE BETWEEN :as_frdate AND :as_todate
AND B.ITEM_CODE  LIKE    :as_item_code 

UNION ALL
SELECT B.DELIV_DATE                               AS SLIP_GUBUN
,B.ITEM_CODE                                      AS ITEM_CODE
,C.ITEM_HNAME                                     AS ITEM_HNAME
,TO_CHAR(C.ITEM_BOX_QTY)||'/'||C.ITEM_HFGOOD_TYPE AS ITEM_TYPE
,B.QTY                                            AS TAX_QTY
,B.EXTRA_QTY                                      AS ADD_QTY
,B.AMOUNT                                         AS AMOUNT
FROM SALES_ORDER_EXP_RTN A
,SALES_ITEM_EXP_RTN  B
,ITEM_1         C
WHERE A.ORDER_DATE = B.ORDER_DATE
AND A.ORDER_NO   = B.ORDER_NO
AND A.SLIP_GUBUN = B.SLIP_GUBUN
AND B.ITEM_CODE  = C.ITEM_CODE
AND B.DELIV_DATE BETWEEN :as_frdate AND :as_todate
AND B.ITEM_CODE  LIKE    :as_item_code 
)
 GROUP BY ITEM_CODE
,ITEM_HNAME
,ITEM_TYPE

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------   수정 완료한 쿼리

 

SELECT SUBSTR(ITEM_CODE, 1, 4) AS ITEM_HCODE
,CASE WHEN 0<INSTR(ITEM_HNAME,'-',1,1) THEN SUBSTR(ITEM_HNAME,1,INSTR(ITEM_HNAME,'-',1,1)-1) ELSE ITEM_HNAME END AS ITEM_HNAME
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '01', TAX_QTY + ADD_QTY, 0)) AS DSP01_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '01', AMOUNT, 0)) AS DSP01_Price
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '02', TAX_QTY + ADD_QTY, 0)) AS DSP02_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '02', AMOUNT, 0)) AS DSP02_Price
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '03', TAX_QTY + ADD_QTY, 0)) AS DSP03_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '03', AMOUNT, 0)) AS DSP03_Price
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '04', TAX_QTY + ADD_QTY, 0)) AS DSP04_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '04', AMOUNT, 0)) AS DSP04_Price
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '05', TAX_QTY + ADD_QTY, 0)) AS DSP05_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '05', AMOUNT, 0)) AS DSP05_Price
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '06', TAX_QTY + ADD_QTY, 0)) AS DSP06_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '06', AMOUNT, 0)) AS DSP06_Price
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '07', TAX_QTY + ADD_QTY, 0)) AS DSP07_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '07', AMOUNT, 0)) AS DSP07_Price
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '08', TAX_QTY + ADD_QTY, 0)) AS DSP08_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '08', AMOUNT, 0)) AS DSP08_Price
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '09', TAX_QTY + ADD_QTY, 0)) AS DSP09_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '09', AMOUNT, 0)) AS DSP09_Price
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '10', TAX_QTY + ADD_QTY, 0)) AS DSP10_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '10', AMOUNT, 0)) AS DSP10_Price
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '11', TAX_QTY + ADD_QTY, 0)) AS DSP11_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '11', AMOUNT, 0)) AS DSP11_Price
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '12', TAX_QTY + ADD_QTY, 0)) AS DSP12_Count
,SUM(DECODE(SUBSTR(DELIV_DATE, 5, 2), '12', AMOUNT, 0)) AS DSP12_Price
FROM
(
SELECT B.DELIV_DATE
,B.ITEM_CODE
,C.ITEM_HNAME
,TO_CHAR(C.ITEM_BOX_QTY)||'/'||C.ITEM_HFGOOD_TYPE AS ITEM_TYPE
,DECODE(A.SLIP_GUBUN, '21', -1, '22', -1, '23', -1, '2A', -1, '41', -1, '51', -1, 1) * (B.TAX_QTY + B.BULK_QTY / DECODE(B.ITEM_CODE, 'HDPL0500', 500, 'RBSS0500', 500, 'UCML0001', 500, 'DPFL0001', 500, C.ITEM_BOX_QTY)) * C.ITEM_BOX_QTY  AS TAX_QTY
,DECODE(A.SLIP_GUBUN, '21', -1, '22', -1, '23', -1, '2A', -1, '41', -1, '51', -1, 1) * B.ADD_QTY * C.ITEM_BOX_QTY                                                                                         AS ADD_QTY
,DECODE(A.SLIP_GUBUN, '21', -1, '22', -1, '23', -1, '2A', -1, '41', -1, '51', -1, 1) * DECODE(:as_flag2, '1', B.SUPPLY_AMT, B.TAX_AMT)                                                   AS AMOUNT
FROM DELIVERY_ORDER A
,DELIVERY_ITEM  B
,ITEM_1         C
WHERE A.ORDER_DATE = B.ORDER_DATE
AND A.ORDER_NO   = B.ORDER_NO
AND A.SLIP_GUBUN <> '41'
AND A.SLIP_GUBUN <> '51'
AND B.ITEM_CODE  = C.ITEM_CODE
AND B.DELIV_DATE BETWEEN :as_frdate AND :as_todate
AND B.ITEM_CODE  LIKE    :as_item_code

UNION ALL
SELECT B.DELIV_DATE                               AS SLIP_GUBUN
,B.ITEM_CODE                                      AS ITEM_CODE
,C.ITEM_HNAME                                     AS ITEM_HNAME
,TO_CHAR(C.ITEM_BOX_QTY)||'/'||C.ITEM_HFGOOD_TYPE AS ITEM_TYPE
,B.QTY*C.ITEM_BOX_QTY                             AS TAX_QTY
,B.EXTRA_QTY * C.ITEM_BOX_QTY                     AS ADD_QTY
,B.AMOUNT                                         AS AMOUNT
FROM SALES_ORDER_EXP A
,SALES_ITEM_EXP  B
,ITEM_1         C
WHERE A.ORDER_DATE = B.ORDER_DATE
AND A.ORDER_NO   = B.ORDER_NO
AND A.SLIP_GUBUN = B.SLIP_GUBUN
AND B.ITEM_CODE  = C.ITEM_CODE
AND B.DELIV_DATE BETWEEN :as_frdate AND :as_todate
AND B.ITEM_CODE  LIKE    :as_item_code 

UNION ALL
SELECT B.DELIV_DATE                               AS SLIP_GUBUN
,B.ITEM_CODE                                      AS ITEM_CODE
,C.ITEM_HNAME                                     AS ITEM_HNAME
,TO_CHAR(C.ITEM_BOX_QTY)||'/'||C.ITEM_HFGOOD_TYPE AS ITEM_TYPE
,B.QTY * C.ITEM_BOX_QTY                                         AS TAX_QTY
,B.EXTRA_QTY * C.ITEM_BOX_QTY                                   AS ADD_QTY
,B.AMOUNT                                         AS AMOUNT
FROM SALES_ORDER_EXP_RTN A
,SALES_ITEM_EXP_RTN  B
,ITEM_1         C
 WHERE A.ORDER_DATE = B.ORDER_DATE
AND A.ORDER_NO   = B.ORDER_NO
AND A.SLIP_GUBUN = B.SLIP_GUBUN
AND B.ITEM_CODE  = C.ITEM_CODE
AND B.DELIV_DATE BETWEEN :as_frdate AND :as_todate
AND B.ITEM_CODE  LIKE    :as_item_code 
)
GROUP BY SUBSTR(ITEM_CODE, 1, 4)
        ,CASE WHEN 0<INSTR(ITEM_HNAME,'-',1,1) THEN SUBSTR(ITEM_HNAME,1,INSTR(ITEM_HNAME,'-',1,1)-1) ELSE ITEM_HNAME END

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

 

알게된점

 

원래는 품목명 데이터 처리할떄 이렇게 쿼리를 작성했으나.......

 

REGEXP_SUBSTR(ITEM_HNAME,'[^-]+',1,1) ITEM_HNAME

# 대괄호 안에 ^  는 NOT 포현 , ^문자가 대괄호 밖에서 상ㅇ되면 문자열이 시작을 의미함

# +는 문자패턴이 1개이상 연결될 때를 나타냄

 

는 Oracle 10g 부터 나온 기능이라서.... 구닥다리 회사 서버에서는 사용불가능

 

SUBSTR(ITEM_HNAME,1,CASE WHEN 0<INSTR(ITEM_HNAME,'-',1,1) THEN INSTR(ITEM_HNAME,'-',1,1)-1 ELSE LENGTH(ITEM_HNAME) END ) AS ITEM_HNAME

 

먼저 이렇게 작성했다가.....

 

CASE WHEN 0<INSTR(ITEM_HNAME,'-',1,1) THEN SUBSTR(ITEM_HNAME,1,INSTR(ITEM_HNAME,'-',1,1)-1) ELSE ITEM_HNAME END AS ITEM_HNAME

 

곰곰히 생각해보니 CASE 문 먼저 쓰는게 쿼리 성능이 더좋을꺼같아서 결국 이렇게 적용시켜주었다.