https://blog.naver.com/sundooedu/221503687297 Elastic Search : 실시간 검색 및 분석 엔진 [차세대 분산 시스템] 데이터의 규모가 폭발적으로 증가하고 있는 시점이다. 증가하는 데이터의 문제를 해결하는 분산형 검색 및 ... blog.naver.com 나중에 보기

#DB데이터 SELECT NAME3,NAME10,NAME11,NAME12,NAME13 FROM CHECK_VACATION_V2 WHERE NAME13 NOT REGEXP '반차|외근' ORDER BY NAME3 ASC #DB조회결과 SELECT t.NAME3 ,c.Numdate ,t.NAME12 ,t.NAME13 FROM CHECK_VACATION_V2 t JOIN view_calendar c ON c.Numdate BETWEEN DATE_FORMAT(t.NAME10, '%Y-%m-%d') AND DATE_FORMAT(t.NAME11, '%Y-%m-%d') WHERE NAME13 NOT REGEXP '반차|외근' ORDER BY NAME3,c.Numdate ASC ======================..

SELECT GROUP_NAME, REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_01),',',' '),'(Meeting)','') AS DATE_01, REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_02),',',' '),'(Meeting)','') AS DATE_02, REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_03),',',' '),'(Meeting)','') AS DATE_03, REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_04),',',' '),'(Meeting)','') AS ..

-- 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_COD..

-- 1,2 컬럼값이 서로 같고 3컬럼값만 다른경우 SELECT * FROM A, B WHERE (A.1 = B.1 AND A.2 = B.2) AND A.3 B.3; -- 1,3컬럼값은 같고 2컬럼값만 다른 경우 SELECT * FROM A, B WHERE (A.1 = B.1 AND A.3 = B.3) AND A.2 B.2; -- 2,3컬럼값은 같고 1컬럼값만 다른 경우 SELECT * FROM A, B WHERE (A.2 = B.2 AND A.3 = B.3) AND A.1 B.1; -- 1,2,3 컬럼이 전부 다른 경우 SELECT * FROM A, B WHERE A.1 B.1 AND A.2 B.2 AND A.3 B.3; 출처 - http://www.gurubee.net/article/59245 ---..

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 CONT..

* 한 컬럼 중복제거 후 이어 붙이기 SELECT LAW_ID, SUBSTR(XMLAGG(SYS.XMLTYPE.CREATEXML(',' || CONTRACT_GESTALT||'') ORDER BY CONTRACT_GESTALT).EXTRACT('/a1/text()'), 2) CONTRACT_GESTALT, SUBSTR(XMLAGG(SYS.XMLTYPE.CREATEXML(',' || MEMO1||'') 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_GESTAL..

없는 데이터 강제로 만들기 및 정해진 데이터 표로 리스트 뿌리기 미련하면서도 억지로 만든쿼리 SELECT * FROM ( SELECT SUBSTR(MARKETING_DATE,0,7) AS DAYS, G_CATE, COUNT(G_CATE) AS CNT FROM TB_MARKETING WHERE SUBSTR(MARKETING_DATE,0,4) = SUBSTR(TO_CHAR(SYSDATE,'YYYYMMDD'),0,4) AND G_CATE IN ('공제회','국가기관','생손보사','은행(중앙회)','금융기관','기타') GROUP BY SUBSTR(MARKETING_DATE,0,7),G_CATE UNION ALL SELECT DAYS,G_CATE,CNT FROM APLUS_V_TB_MARKETING_ANNUA..

1. 데이터를 옮길 대상 테이블을 생성한다. : "TEMP_20130607" 라하자.; CREATE TABLE TEMP_20130607 ( TXDATE VARCHAR2(8 BYTE) NOT NULL, WRAP_ACCOUNT_NO VARCHAR2(20 BYTE) NOT NULL, WRAP_ACCOUNT_AMOUNT VARCHAR2(4000 BYTE) ) TABLESPACE APLUS_DATA_TS PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL ..