[Oracle] 다중 LIKE(REGEXP_LIKE)

Database 2018.06.12 08:13 Posted by 애플자라
EDIT EM_MMT_LOG_201806
WHERE TRAN_ETC2='MASS'
AND REGEXP_LIKE(TRAN_ETC1, '2018060800001|2018061100001|2018061100002')
--AND TRAN_ETC1 (LIKE '%2018060800001%' or LIKE '%2018061100001%' or LIKE '%2018061100002%')

 

[ORACLE]COUNT DECODE

Database 2018.06.04 09:23 Posted by 애플자라

SELECT '신청: '||TOTAL||'건(전체), '||P_TYPE||'건(PC), '||M_TYPE||'건(모바일)' AS EVENT_M

FROM

(

SELECT COUNT (1) AS TOTAL,

       COUNT (DECODE (TYPE, 'P', 'P')) AS P_TYPE,

       COUNT (DECODE (TYPE, 'M', 'M')) AS M_TYPE

  FROM N_BOARD_EVENT

)

[ORACLE]WMSYS.WM_CONCAT 월별데이터 합치기

Database 2018.04.06 09:17 Posted by 애플자라

SELECT 
    GROUP_NAME,
    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_01),',','<br/>'),'(Meeting)','') AS DATE_01,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_02),',','<br/>'),'(Meeting)','') AS DATE_02,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_03),',','<br/>'),'(Meeting)','') AS DATE_03,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_04),',','<br/>'),'(Meeting)','') AS DATE_04,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_05),',','<br/>'),'(Meeting)','') AS DATE_05,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_06),',','<br/>'),'(Meeting)','') AS DATE_06,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_07),',','<br/>'),'(Meeting)','') AS DATE_07,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_08),',','<br/>'),'(Meeting)','') AS DATE_08,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_09),',','<br/>'),'(Meeting)','') AS DATE_09,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_10),',','<br/>'),'(Meeting)','') AS DATE_10,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_11),',','<br/>'),'(Meeting)','') AS DATE_11,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_12),',','<br/>'),'(Meeting)','') AS DATE_12

FROM (SELECT GROUP_NAME,
       MARKETING_DATE,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '01' THEN MARKETING_DATE END MARKETING_DATE_01,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '02' THEN MARKETING_DATE END MARKETING_DATE_02,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '03' THEN MARKETING_DATE END MARKETING_DATE_03,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '04' THEN MARKETING_DATE END MARKETING_DATE_04,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '05' THEN MARKETING_DATE END MARKETING_DATE_05,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '06' THEN MARKETING_DATE END MARKETING_DATE_06,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '07' THEN MARKETING_DATE END MARKETING_DATE_07,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '08' THEN MARKETING_DATE END MARKETING_DATE_08,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '09' THEN MARKETING_DATE END MARKETING_DATE_09,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '10' THEN MARKETING_DATE END MARKETING_DATE_10,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '11' THEN MARKETING_DATE END MARKETING_DATE_11,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '12' THEN MARKETING_DATE END MARKETING_DATE_12,
       ROW_NUMBER () OVER (PARTITION BY GROUP_NAME ORDER BY MARKETING_DATE) RNUM
  FROM TB_MARKETING
 WHERE     SUBSTR (MARKETING_DATE, 0, 4) = '2018'
       --AND GROUP_NAME = '건설기술용역공제조합' 
     )
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR GROUP_NAME = GROUP_NAME
GROUP BY GROUP_NAME
ORDER BY GROUP_NAME ASC

 

출처 - http://blog.naver.com/PostView.nhn?blogId=jaejin1028&logNo=220793468335 

 

[ORACLE]그룹함수로 중복된 컬럼 제거하기

Database 2017.11.08 15:16 Posted by 애플자라

-- 2건이상 데이터
SELECT  MAX(ID) AS ID, MAX(TIMS_DATE) AS TIMS_DATE, MAX(FUND_CODE) AS FUND_CODE, COUNT(*) AS CNT
  FROM IFT_FUND_STANDARD_MODIFY
  WHERE FUND_CODE ='01032'
GROUP BY TIMS_DATE||FUND_CODE
HAVING COUNT(*) > 1

 

-- 2건이상 데이터중 과거 데이터 삭제
DELETE  -- SELECT꼭 해보고 날려버린다
FROM IFT_FUND_STANDARD_MODIFY
WHERE TIMS_DATE||FUND_CODE
IN
(
    SELECT TIMS_DATE||FUND_CODE
    FROM IFT_FUND_STANDARD_MODIFY
    WHERE FUND_CODE ='01032'
    GROUP BY TIMS_DATE||FUND_CODE
    HAVING COUNT(*) > 1
)
AND ID NOT IN (
SELECT  MAX(ID) as ID
  FROM IFT_FUND_STANDARD_MODIFY
  WHERE FUND_CODE ='01032'
GROUP BY TIMS_DATE||FUND_CODE
HAVING COUNT(*) > 1
)

 

CONCAT(TIMS_DATE,FUND_CODE) 도 가능

 

출처 - http://linguist79.tistory.com/49

[ORACLE]CLOB DATA Export Excel(DBMS_LOB.SUBSTR)

Database 2016.03.28 15:09 Posted by 애플자라

CLOB 데이터가 원하는데로 안나와서 일단 미련하게 추출; 


 SELECT 

   BOARD_SEQ,

         SEQ,

         TITLE,

         DBMS_LOB.SUBSTR(CNTNT, 2000, 1)  AS CONTENTS1,

         DBMS_LOB.SUBSTR(CNTNT, 2000, 2001)     AS CONTENTS2,

         DBMS_LOB.SUBSTR(CNTNT, 2000, 4001)     AS CONTENTS3,

         DBMS_LOB.SUBSTR(CNTNT, 2000, 6001)     AS CONTENTS4,

         DBMS_LOB.SUBSTR(CNTNT, 2000, 8001)     AS CONTENTS5,

         DBMS_LOB.SUBSTR(CNTNT, 2000, 10001)    AS CONTENTS6,

         DBMS_LOB.SUBSTR(CNTNT, 2000, 12001)    AS CONTENTS7,

         DBMS_LOB.SUBSTR(CNTNT, 2000, 14001)    AS CONTENTS8,

         DBMS_LOB.SUBSTR(CNTNT, 2000, 16001)    AS CONTENTS9

         --dbms_lob.getlength(CNTNT) -- 15801

    FROM TBD_BOARD

   WHERE BOARD_SEQ = '3'

ORDER BY SEQ DESC

[ORACLE]조인 결과로 테이블 UPDATE하기

Database 2014.10.29 16:54 Posted by 애플자라

UPDATE TB_MARKETING AA

SET (AGENCY_ID) = (SELECT AGENCY_ID FROM TEMP_GROUP B WHERE B.GROUP_NAME =AA.GROUP_NAME)

 

 

SELECT E.ID, E.SUBID, T.SUBID

UPDATE TB_MARKETING SET SUBID = T.SUBID

FROM TB_MARKETING E

INNER JOIN #TEMP_GROUP T ON ( E.SEQ = T.SEQ )

[ORACLE]FLASH BACK을 이용한 데이터복구

Database 2014.10.29 14:31 Posted by 애플자라

 

오라클을 이용하다면 보면 의도치 않게 데이터를 삭제하거나 변경을 하고 커밋을 해버리는 경우가 종종 발생하는데요.

이런 경우 데이터를 COMMIT을 한지 얼마 안 되었거나 메모리를 넉넉하게 잡아놓은 경우 FLASHBACK 기술을 이용해서 데이터 복원이 가능합니다.


1. 먼제 임시 테이블과 데이터를 생성합니다.






2. 데이터를 삭제하고 COMMIT을 합니다.




3. FLASHBACK에 데이터가 존재하는 확인합니다. 

본 포스팅에서 사용할 명령어는 AS OF TABLE 입니다. 자세히 기술하면 다음과 같습니다.


SELECT * FROM TEMP_TABLE

  AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '8' MINUTE); 여기에서 '8'은 몇분전에 데이터를 조회할지를 표시하는 것으로 본 포스팅에서 사용된 쿼리는 8번 데이터를 조회한다는 뜻입니다. 여기서 보여줄 수 있는 데이터의 인터벌은 시스템 설정 및 운영에 따라 달라질 수 있습니다.




   4. 데이터가 조회가 된다면 원래있던 테이블에 새롭게 INSERT 하는 방법 등으로 데이터를 복구 할 수 있습니다.

 

SELECT * FROM NEWYN_UPDATE_TRG01_LOG

AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '8' MINUTE)

SELECT * FROM LAW_OP_FUND
AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1575' MINUTE)

 

출처 - http://hothobbang.tistory.com/63

[ORACLE]Unique 번호를 남길때

Database 2014.10.02 10:40 Posted by 애플자라

SELECT

    ROW_NUMBER () OVER (ORDER BY NAME ASC) EMS_M_ID,

    NAME,

    EMAIL    

FROM

    테이블명

CREATE OR REPLACE TRIGGER APLUSORA.NEWYN_UPDATE_TRG01

 AFTER UPDATE OF CONFIRM5 ON APLUSORA.CUSTOMER

 FOR EACH ROW

 BEGIN

    if :NEW.CONFIRM5 = 'N' then -- CONFIRM5 ='N' 경우만

         UPDATE APLUSORA.NEWS_CUST

            SET NEWS_YN = 'N'

         WHERE  EMAIL = :NEW.EMAIL

         AND    NEWS_YN = 'Y';

         UPDATE APLUSORA.FAIR_CUSTOMER

            SET NEWS_YN = 'N'

         WHERE  FC_EMAIL = :NEW.EMAIL

         AND    NEWS_YN = 'Y';

         INSERT INTO NEWYN_UPDATE_TRG01_LOG (EMAIL, CONFIRM5, ACTION)

         VALUES(:NEW.EMAIL, :NEW.CONFIRM5, 'UPDATE');

    end if;

 END;

/

 

CREATE OR REPLACE TRIGGER APLUSORA.NEWYN_DELETE_TRG01

 AFTER DELETE ON APLUSORA.CUSTOMER

 FOR EACH ROW

 BEGIN

    UPDATE APLUSORA.NEWS_CUST

    SET NEWS_YN = 'N'

    WHERE  EMAIL = :OLD.EMAIL;

    UPDATE APLUSORA.FAIR_CUSTOMER

    SET NEWS_YN = 'N'

    WHERE  FC_EMAIL = :OLD.EMAIL;

    INSERT INTO NEWYN_UPDATE_TRG01_LOG (EMAIL, CONFIRM5, ACTION)

    VALUES(:OLD.EMAIL, :OLD.CONFIRM5, 'DELETE');

 END;

/

 

-- 사기꾼한테 피해를 입지 않게 트리거로그도 동시에 남김!

CREATE TABLE APLUSORA.NEWYN_UPDATE_TRG01_LOG

(

  EMAIL        VARCHAR2(50 BYTE),

  CONFIRM5     VARCHAR2(1 BYTE),

  ACTION       VARCHAR2(6 BYTE),

  REGISTER_DT  DATE DEFAULT SYSDATE

)

 

BEFORE : 데이터 처리가 실행되기 전

AFTER : 데이터 처리가 실행 된 후

FOR EACH ROW : 데이터 처리시 건건이 모두 트리거 실행

:OLD.컬럼명 : SQL 반영전의 컬럼 데이터

:NEW.컬럼명 : SQL 반영 후의 컬럼 데이터

 

* 한 컬럼 중복제거 후 이어 붙이기

 

SELECT LAW_ID,

       SUBSTR(XMLAGG(SYS.XMLTYPE.CREATEXML('<'||FLAG||'>,' || CONTRACT_GESTALT||'</'||FLAG||'>') ORDER BY CONTRACT_GESTALT).EXTRACT('/a1/text()'), 2) CONTRACT_GESTALT,

       SUBSTR(XMLAGG(SYS.XMLTYPE.CREATEXML('<'||FLAG||'>,' || MEMO1||'</'||FLAG||'>') ORDER BY MEMO1).EXTRACT('/a1/text()'), 2) MEMO1

 FROM (

       SELECT

           LAW_ID,

           CONTRACT_GESTALT,NVL2(CONTRACT_GESTALT, 'a' || ROW_NUMBER() OVER (PARTITION BY LAW_ID, CONTRACT_GESTALT ORDER BY CONTRACT_GESTALT),

           'a2')  AS FLAG,

           MEMO1,NVL2(MEMO1, 'a' || ROW_NUMBER() OVER (PARTITION BY LAW_ID, MEMO1 ORDER BY MEMO1),

           'a3')  AS FLAG1

       FROM LAW_OP_FUND)      

GROUP BY LAW_ID

 

* 중복제거 없이 이어 붙이기

 

SELECT 

    LAW_ID,

    SUBSTR (MAX (SYS_CONNECT_BY_PATH (CONTRACT_GESTALT, ',')), 2) CONTRACT_GESTALT

FROM (SELECT LAW_ID, CONTRACT_GESTALT,

       ROW_NUMBER () OVER (PARTITION BY LAW_ID ORDER BY CONTRACT_GESTALT) rnum

       FROM LAW_OP_FUND)

START WITH rnum = 1

CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR LAW_ID = LAW_ID

GROUP BY LAW_ID

 

또는

 

SELECT

    LAW_ID,

    MAX(LTRIM(SYS_CONNECT_BY_PATH(CONTRACT_GESTALT,','),',')) AS CONTRACT_GESTALT

FROM

    (

    SELECT LAW_ID,CONTRACT_GESTALT,ROW_NUMBER() OVER(PARTITION BY LAW_ID ORDER BY ROWNUM) AS RN

    FROM LAW_OP_FUND T1

    --where LAW_ID ='2014091700002'

    )

START WITH RN = 1

CONNECT BY PRIOR LAW_ID = LAW_ID AND PRIOR RN = RN - 1

GROUP BY LAW_ID

ORDER BY LAW_ID ASC

 

출처 - http://amnesis.tistory.com/13