[DB2 ALTER 사용법]
[테이블 생성]+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//PART.1
CREATE TABLE COMMON.COSTATE(
REGCODE VARCHAR(4) NOT NULL,
STDATE VARCHAR(8) NOT NULL,
CSSEQ VARCHAR(5) NOT NULL,
RPTATION VARCHAR(4),
ACCNT VARCHAR(4),
DISACC VARCHAR(20),
REGNO VARCHAR(13),
CPNAME VARCHAR(50),
CPDATE VARCHAR(8),
CPTEL VARCHAR(20),
CPADDR VARCHAR(100),
STATUS VARCHAR(100),
ITEMFD VARCHAR(20),
TAXATION VARCHAR(100),
OFFICE VARCHAR(100),
MBANK VARCHAR(20),
DPSTKIND VARCHAR(20),
ACCNUM VARCHAR(20),
CONSTRAINT COMMON.COSTATE_PK PRIMARY KEY (REGCODE,STDATE,CSSEQ))
//PART.2
LABEL ON COLUMN COMMON.COSTATE (
REGCODE IS '사업자코드',
STDATE IS '기준일자',
CSSEQ IS '순번',
RPTATION IS '대표',
ACCNT IS '회계',
DISACC IS '관할계좌',
REGNO IS '사업자등록번호',
CPNAME IS '사업장명',
CPDATE IS '사업개시일',
CPTEL IS '사업장전화',
CPADDR IS '사업장주소',
STATUS IS '업태',
ITEMFD IS '종목',
TAXATION IS '관할세무서',
OFFICE IS '관할시(군)구청',
MBANK IS '주거래은행',
DPSTKIND IS '예금종류',
ACCNUM IS '계좌번호')
//PART.3
LABEL ON COLUMN COMMON.COSTATE (
REGCODE TEXT IS '사업자코드',
STDATE TEXT IS '기준일자',
CSSEQ TEXT IS '순번',
RPTATION TEXT IS '대표',
ACCNT TEXT IS '회계',
DISACC TEXT IS '관할계좌',
REGNO TEXT IS '사업자등록번호',
CPNAME TEXT IS '사업장명',
CPDATE TEXT IS '사업개시일',
CPTEL TEXT IS '사업장전화',
CPADDR TEXT IS '사업장주소',
STATUS TEXT IS '업태',
ITEMFD TEXT IS '종목',
TAXATION TEXT IS '관할세무서',
OFFICE TEXT IS '관할시(군)구청',
MBANK TEXT IS '주거래은행',
DPSTKIND TEXT IS '예금종류',
ACCNUM TEXT IS '계좌번호')
[컬럼 추가시]++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ALTER TABLE TAXB0 ALTER COLUMN HAL_STATUS CHAR(1) DEFAULT '0'
ALTER TABLE TNAME ADD CNAME CHAR(1) DEFAULT '0'
[컬럼 수정시]++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ALTER TABLE TNAME ALTER CNAME SET DATA TYPE VARCHAR(100);
ALTER TABLE TABNAME ALTER COLUMN SET DATA TYPE (VARCHAR/NUMBER..ETC)(LENTH);
[원하는 데이터만 입력할경우]++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
INSERT INTO COMMON.COSTATE(MBANKCD,DPSTKINDCD) VALUES('11','15')
[원하는 데이터만 수정할경우]++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
UPDATE COMMON.COSTATE SET MBANKCD = '11'
WHERE CSSEQ = 2
[라벨네임.1]++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
LABEL ON COLUMN COMMON.COSTATE (
MBANKCD IS '주거래은행코드',
DPSTKINDCD IS '에금코드')
[라벨네임.2]++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
LABEL ON COLUMN COMMON.COSTATE (
MBANKCD TEXT IS '주거래은행코드',
DPSTKINDCD TEXT IS '에금코드')
var gsAcc = "/services/servlet/ktdci5.acc.";
[서브쿼리]++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT
IFNULL(A.GGIBU,0)+IFNULL(A.GGIBU2,0)+IFNULL(A.GGIBU3,0)+IFNULL(A.GGIBU4,0)+IFNULL(A.SGIBU4,0) AS TOTGIBU,
B.GEUMAEK,
C.GNOJO
FROM PAYROLL.PBTAXMST1 A
LEFT OUTER JOIN (
SELECT
PROCYM,EMPNO,SUM (GEUMAEK) AS GEUMAEK
FROM PAYROLL.PBTAXGST
WHERE PROCYM = '2006'
AND EMPNO = '95301276'
GROUP BY PROCYM,EMPNO ) B ON A.EMPNO = B.EMPNO
AND A.APPCY = B.PROCYM
LEFT OUTER JOIN (
SELECT
APPCY,EMPNO,GNOJO
FROM PAYROLL.PBPAYADD
WHERE APPCY = '2006'
AND EMPNO = '95301276' ) C ON A.EMPNO = C.EMPNO
AND A.APPCY = C.APPCY
WHERE A.APPCY = '2006'
AND A.EMPNO = '95301276'
[ ]++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++