오라클

EDIT N_FUND_GOOD A WHERE DEL_YN = 'N' AND USE_YN = 'Y' ORDER BY DECODE(GOOD_TYPE,'FD101',1,'FD102',2,'FD103',3,'FD109',4,'FD104',5) , FUND_CODE ASC

SELECT ROW_NUMBER() OVER (ORDER BY NAME ASC) AS EMS_M_ID, NAME AS EMS_M_NAME, TRIM(REPLACE(MOBILE,'-','')) AS EMS_M_PHONE FROM APLUSORA.TEMP_N_BOARD_EVENT ORDER BY NAME ASC

SELECT TO_CHAR (REG_DATE, 'YYYY-MM-DD') AS "접수일", COUNT (1) "접수건수", SUM (COUNT (1)) OVER (ORDER BY TO_CHAR (REG_DATE, 'YYYY-MM-DD')) "누적접수건수" FROM N_BOARD_EVENT GROUP BY TO_CHAR (REG_DATE, 'YYYY-MM-DD') ORDER BY TO_CHAR (REG_DATE, 'YYYY-MM-DD')

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%')

SELECT '신청: '||TOTAL||'건(전체), '||P_TYPE||'건(PC), '||M_TYPE||'건(모바일)' AS EVENT_MFROM(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)

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

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

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 )

오라클을 이용하다면 보면 의도치 않게 데이터를 삭제하거나 변경을 하고 커밋을 해버리는 경우가 종종 발생하는데요. 이런 경우 데이터를 COMMIT을 한지 얼마 안 되었거나 메모리를 넉넉하게 잡아놓은 경우 FLASHBACK 기술을 이용해서 데이터 복원이 가능합니다. 1. 먼제 임시 테이블과 데이터를 생성합니다. 2. 데이터를 삭제하고 COMMIT을 합니다. 3. FLASHBACK에 데이터가 존재하는 확인합니다. 본 포스팅에서 사용할 명령어는 AS OF TABLE 입니다. 자세히 기술하면 다음과 같습니다. SELECT * FROM TEMP_TABLE AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '8' MINUTE); 여기에서 '8'은 몇분전에 데이터를 조회할지를 표시하는 것으로 본..