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

Database 2018.04.06 09:17 Posted by 애플자라

SELECT 
    GROUP_NAME,
    WMSYS.WM_CONCAT(MARKETING_DATE_01) AS DATE_01,
    WMSYS.WM_CONCAT(MARKETING_DATE_02) AS DATE_02,
    WMSYS.WM_CONCAT(MARKETING_DATE_03) AS DATE_03,
    WMSYS.WM_CONCAT(MARKETING_DATE_04) AS DATE_04,
    WMSYS.WM_CONCAT(MARKETING_DATE_05) AS DATE_05,
    WMSYS.WM_CONCAT(MARKETING_DATE_06) AS DATE_06,
    WMSYS.WM_CONCAT(MARKETING_DATE_07) AS DATE_07,
    WMSYS.WM_CONCAT(MARKETING_DATE_08) AS DATE_08,
    WMSYS.WM_CONCAT(MARKETING_DATE_09) AS DATE_09,
    WMSYS.WM_CONCAT(MARKETING_DATE_10) AS DATE_10,
    WMSYS.WM_CONCAT(MARKETING_DATE_11) AS DATE_11,
    WMSYS.WM_CONCAT(MARKETING_DATE_12) 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

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

 

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

 

SELECT A.TIMS_DATE,
       A.FUND_CODE,
       A.FUND_NAME,
       A.BM_INDEX,
       B.BM_INDEX,
       A.BM_RATE,
       B.BM_RATE
  FROM IFT_FUND_STANDARD_PRICE_RESULT A, IFT_FUND_STANDARD_MODIFY B
 WHERE     (A.BM_INDEX <> B.BM_INDEX OR A.BM_RATE <> B.BM_RATE)
       --AND A.TIMS_DATE = '20171104'
       AND A.TIMS_DATE = TO_CHAR(SYSDATE-3, 'YYYYMMDD')
       AND A.TIMS_DATE = B.TIMS_DATE
       AND A.FUND_CODE = B.FUND_CODE

[ORACLE]ALTER COLUMN

Database 2017.10.17 08:34 Posted by 애플자라

-- 일반 컬럼수정
ALTER TABLE EM_MMT_TRAN
MODIFY RECIPIENT_NUM VARCHAR2(16 BYTE)

 

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


-- 일반 컬럼수정(NOT NULL)
-- ORA-01442: column to be modified to NOT NULL is already NOT NULL
ALTER TABLE EM_MMT_TRAN
MODIFY CALLBACK VARCHAR2(16 BYTE) NOT NULL

 

-- DISABLE
ALTER TABLE EM_MMT_TRAN DISABLE CONSTRAINT SYS_C0037363 CASCADE

 

--> 일반 컬럼 수정문 실행

 

-- ENABLE
ALTER TABLE EM_MMT_TRAN ENABLE CONSTRAINT SYS_C0037363 

 

SYS_C0037363 Check    Enabled        Not Deferrable    Immediate    Validated            1    "CALLBACK" IS NOT NULL

 

SELECT
 운용사펀드코드,
 (SELECT 설정일 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\20170703\설정일생성.xlsx;','SELECT * FROM [Sheet1$]') WHERE 펀드코드 = A.운용사펀드코드) AS 설정일
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\20170703\펀드설정전체.xlsx;','SELECT * FROM [Sheet1$]') A

----------------------------------------------- 기본셋팅

1. Ad Hoc Distributed Queries 옵션 활성화

EXEC sp_configure 'show advanced options', 1

GO

 

RECONFIGURE

GO

 

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

GO

 

RECONFIGURE

GO

2. Mirosoft Access Database Engine 2010 Redistributable  설치

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255

자 이제 준비는 다 되었다. 이제 쿼리로 엑셀을 테이블처럼 사용하면 된다.


출처 - http://yoonsy.tistory.com/12

[MYSQL]원격지 SELECT

Database 2017.02.07 08:29 Posted by 애플자라

CREATE TABLE `tb_result_117` (
  `STAND_DATE` varchar(8) NOT NULL COMMENT '작업일자',
  `ITEM_CD` char(12) NOT NULL COMMENT '배치작업의 코드',
  `SUB_SECTION` varchar(32) NOT NULL COMMENT '배치작업의 코드 하부 작업(하부분류또는 파일명)',
  `START_TIME` varchar(6) NOT NULL COMMENT '시작시간',
  `END_TIME` varchar(6) DEFAULT NULL COMMENT '마감시간',
  `RESULT_MESSAGE` varchar(512) DEFAULT NULL COMMENT '처리결과 메시지',
  `TOTAL_COUNT` int(11) DEFAULT NULL COMMENT '전체건수',
  `PROCESSED_COUNT` int(11) DEFAULT NULL COMMENT '처리건수',
  `PROCESS_NAME` varchar(50) DEFAULT NULL,
  `SEND_DATE` datetime DEFAULT NULL,
  `FLAG` char(1) CHARACTER SET latin1 DEFAULT 'N',
  PRIMARY KEY (`STAND_DATE`,`ITEM_CD`,`SUB_SECTION`,`START_TIME`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT='일별 배치' CONNECTION='mysql://DBID:DB패스워드@DBIP:포트/dg_db_batch/tb_result';

 

참고 - http://itzone.tistory.com/672

 

[MYSQL]지니안NAC 저장장치 정보 쿼리

Database 2017.01.23 16:38 Posted by 애플자라
SELECT
      A.NL_FQDN,
      A.NL_IPSTR,
      /*A.NL_MAC,*/
      (SELECT NI_MOTHERBOARDNAME FROM ALDER.DEVINFOALL_CPU WHERE NI_DEVID = A.NL_DEVID) AS NI_MOTHERBOARDNAME,
      (SELECT NI_NAME FROM ALDER.DEVINFOALL_CPU WHERE NI_DEVID = A.NL_DEVID) AS NI_NAME,
      A.NL_PLATFORM,
      (SELECT CONCAT(ROUND(NI_TOTALMEM/1024/1024,2),' GB') FROM ALDER.DEVINFOALL_MEM WHERE NI_DEVID = A.NL_DEVID) AS NI_TOTALMEM,
      B.NI_NAME AS DISK_NAME,
      B.NI_VENDOR AS DISK_VENDOR,
      B.NI_MODEL AS DISK_MODEL,
      CASE
        WHEN INSTR(B.NI_MODEL, 'SSD') = '0' THEN "일반"
        WHEN INSTR(B.NI_MODEL, 'SSD') > '0' THEN "SSD"
      END GUBUN
FROM ALDER.NODELIST A, ALDER.DEVINFOALL_STORAGE B
WHERE A.NL_DEVID = B.NI_DEVID
AND B.NI_FILESYSTEM ='NTFS'
ORDER BY A.NL_IPSTR ASC

 

TAG genian, NAC

[MYSQL]DB 로그인(멀티인스턴스)

Database 2016.11.14 08:39 Posted by 애플자라

[MYSQL_HOME]/bin

 

/data1/mysql1/bin/mysql -uroot -p --socket=/tmp/mysql1-3306.sock
/data2/mysql2/bin/mysql -uroot -p --socket=/tmp/mysql2-3307.sock
/data3/mysql3/bin/mysql -uroot -p --socket=/tmp/mysql3-3308.sock

'Database' 카테고리의 다른 글

[MYSQL]원격지 SELECT  (0) 2017.02.07
[MYSQL]지니안NAC 저장장치 정보 쿼리  (0) 2017.01.23
[MYSQL]DB 로그인(멀티인스턴스)  (0) 2016.11.14
[MYSQL]LINUX 시작서비스등록  (0) 2016.11.14
[MYSQL]사용자 계정생성  (0) 2016.11.11
[MYSQL]패스워드 변경  (0) 2016.11.11

[MYSQL]LINUX 시작서비스등록

Database 2016.11.14 08:36 Posted by 애플자라

[MYSQL_HOME]/support-files/mysql.server

cp mysql.server /etc/init.d/mysqld1

[MYSQL]사용자 계정생성

Database 2016.11.11 19:39 Posted by 애플자라

[5.7 이전 버젼]
update mysql.user set password=password('새로운패스워드') where user='root';
FLUSH PRIVILEGES;

[5.7 이후 버젼]
update user set authentication_string=password('새로운패스워드') where user='root';
FLUSH PRIVILEGES;

(5.7 이후 버젼에는 password 필드가 존재하지 않는다.)
설정 후 MySQL 접속 후 명령을 실행하게 되면 다음과 같은 장애가 발생한다.

mysql> SELECT 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

위와 같은 오류가 발생하는 경우 다음과 같은 명령어를 실행한다.

mysql> SET PASSWORD = PASSWORD('새로운패스워드');
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |

 

'Database' 카테고리의 다른 글

[MYSQL]DB 로그인(멀티인스턴스)  (0) 2016.11.14
[MYSQL]LINUX 시작서비스등록  (0) 2016.11.14
[MYSQL]사용자 계정생성  (0) 2016.11.11
[MYSQL]패스워드 변경  (0) 2016.11.11
[MYSQL]multiple instances(mysqld_multi)  (0) 2016.11.08
[MYSQL]ROWNUM  (0) 2016.10.21