--GRANT CREATE VIEW TO SCOTT;
CREATE OR REPLACE VIEW VIEW_NEWSAWON
--뷰테이블을 만드는데 없으면 만들고 있으면 이것으로 대체 해라
AS
SELECT T.SANO AS "사원번호",
T.NAME AS "사원명",
T.JUBUN AS "주민번호",
T.GENDER AS "성별",
T.AGE AS "현재나이",
TRUNC(T.AGE, -1) AS "연령대",
T.YEARPAY AS "연봉",
T.HIREDATE AS "입사일자",
TO_CHAR(ADD_MONTHS(SYSDATE, (60-T.AGE) * 12), 'YYYY')
|| TO_CHAR(T.HIREDATE, '-MM-DD') AS "정년퇴직일",
TRUNC(( T.YEARPAY / 12 ) *
TRUNC( MONTHS_BETWEEN( TO_DATE( TO_CHAR(ADD_MONTHS(SYSDATE, (60-T.AGE) * 12) ,'YYYY')
|| TO_CHAR(T.HIREDATE, '-MM-DD'), 'YYYY-MM-DD'), T.HIREDATE) / 12 )) AS "퇴직금",
TRUNC( SYSDATE - T.HIREDATE ) AS "근무일수",
TRUNC( TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, (60-T.AGE) * 12), 'YYYY')
|| TO_CHAR(T.HIREDATE, '-MM-DD'),'YYYY-MM-DD') - SYSDATE ) AS "남은일수"
FROM
(
SELECT SANO, NAME, JUBUN,
CASE
WHEN SUBSTR(JUBUN, 7, 1) IN ('1','3') THEN '남'
ELSE '여' END AS "GENDER",
EXTRACT(YEAR FROM SYSDATE)
- (CASE
WHEN SUBSTR(JUBUN, 7, 1) IN ('1','2')
THEN TO_NUMBER( SUBSTR(JUBUN, 1, 2) ) + 1900
ELSE TO_NUMBER( SUBSTR(JUBUN, 1, 2) ) + 2000
END )
+ 1 AS "AGE",
NVL2(COMM, SAL*12 + COMM, SAL*12) AS "YEARPAY",
HIREDATE
FROM TBL_NEWSAWON
) T; --여기까지 테이블 뷰
SELECT * FROM VIEW_NEWSAWON;
