SELECT * FROM staff FETCH FIRST 5 ROWS ONLY
라고 하면 하면 처음 5개의 row가 나옵니다.
SELECT bus_mkt_id, svc_mgmt_num, svc_cd, svc_num, cell_equip_modl_cd, line_num
FROM coispc.vcell_num
WHERE svc_mgmt_num = ?
ORDER BY eff_dt_tm desc FETCH FIRST 1 ROWS ONLY
WITH UR
SELECT INTEGER(SUBSTR(CHAR(CURRENT DATE),1,1)||SUBSTR(CHAR(CURRENT DATE),3,2)||SUBSTR(CHAR(CURRENT DATE),6,2)||SUBSTR(CHAR(CURRENT DATE),9,2)),
INTEGER(SUBSTR(CHAR(CURRENT TIMESTAMP),12, 2) || SUBSTR(CHAR(CURRENT TIMESTAMP),15,2) || SUBSTR(CHAR(CURRENT TIMESTAMP),18,2) || SUBSTR(CHAR(CURRENT TIMESTAMP),21,1))
FROM SYSIBM.SYSDUMMY1
WITH UR
CREATE FUNCTION month_between (p_start date, p_end date)
RETURNS SMALLINT
BEGIN atomic
DECLARE v_year_diff SMALLINT DEFAULT 0;
DECLARE v_month_diff SMALLINT DEFAULT 0;
DECLARE v_diff SMALLINT DEFAULT 0;
SET v_year_diff = YEAR(p_start) - YEAR(p_end);
SET v_month_diff = MONTH(p_start) - MONTH(p_end);
IF v_year_diff != 0 THEN
set v_diff = v_year_diff * 12;
END if;
SET v_diff = v_diff + v_month_diff;
RETURN v_diff;
END@
@@@ Oracle의 Decode기능
ex1)
SELECT rownumber,CASE WHEN zip_code BETWEEN '100091' AND '100091' THEN '91'
WHEN zip_code BETWEEN '100092' AND '100092' THEN '92'
WHEN zip_code BETWEEN '100093' AND '100093' THEN '93'
WHEN zip_code BETWEEN '100094' AND '100094' THEN '94'
WHEN zip_code BETWEEN '100095' AND '100095' THEN '95'
ELSE '99'
END
FROM (
SELECT zip_code,ROWNUMBER() OVER (ORDER BY zip_code) AS rownumber
FROM zipcode
) AS t
WHERE ROWNUMBER BETWEEN 20 AND 30
ex2)
SELECT ROWNUMBER,CASE zip_code
WHEN '100091' THEN '91'
WHEN '100092' THEN '92'
WHEN '100093' THEN '93'
WHEN '100094' THEN '94'
WHEN '100095' THEN '95'
ELSE '99'
END
FROM (
SELECT zip_code,ROWNUMBER() OVER (ORDER BY zip_code) AS rownumber
FROM zipcode
) AS t
WHERE ROWNUMBER BETWEEN 20 AND 30
@@@ INTEGER형으로 변환
ex)
SELECT INTEGER(zip_code)
FROM zipcode
FETCH FIRST 5 ROWS ONLY
@@@ DOUBLE형으로 변환
ex)
SELECT DOUBLE(zip_code)
FROM zipcode
FETCH FIRST 5 ROWS ONLY
@@@ SUBSTR
ex)
SELECT SUBSTR(zip_code,1,3)
FROM zipcode
FETCH FIRST 5 ROWS ONLY
@@@ CHAR
ex)
SELECT CHAR(doseo)
FROM zipcode
FETCH FIRST 5 ROWS ONLY
@@@ COALESCE - Oracle Nvl()기능
컬럼 타입에 따라 인수를 결정한다. COALESCE(문자형,문자형표시) COALESCE(숫자형,숫자형표시)
ex)
SELECT COALESCE(doseo,'1')
FROM zipcode
FETCH FIRST 5 ROWS ONLY
@@@ ||
문자연결기능
SELECT COALESCE(doseo,'1') || zip_code
FROM zipcode
FETCH FIRST 5 ROWS ONLY
@@@ page기능
SELECT t.zip_code,page
FROM (
SELECT zip_code,((ROWNUMBER() OVER() -1)/ 10+1) AS page
FROM zipcode
) AS t
WHERE t.page = 3
FETCH FIRST 100 ROWS ONLY
WITH UR
@@@ year 구하기
ex1)
SELECT YEAR(CURRENT TIMESTAMP)
FROM SYSIBM.SYSDUMMY1
WITH UR
ex2)
SELECT YEAR('2004-05-16')
FROM SYSIBM.SYSDUMMY1
WITH UR
ex3)
SELECT YEAR(CURRENT DATE)
FROM SYSIBM.SYSDUMMY1
WITH UR
@@@ month 구하기
ex1)
SELECT MONTH(CURRENT TIMESTAMP)
FROM SYSIBM.SYSDUMMY1
WITH UR
ex2)
SELECT MONTH('2004-08-16')
FROM SYSIBM.SYSDUMMY1
WITH UR
@@@ day 구하기
ex1)
SELECT DAY(CURRENT TIMESTAMP)
FROM SYSIBM.SYSDUMMY1
WITH UR
EX2)
SELECT DAY('2004-08-16')
FROM SYSIBM.SYSDUMMY1
WITH UR
@@@ week 구하기
ex)
SELECT WEEK('2004-05-16')
FROM SYSIBM.SYSDUMMY1
WITH UR
@@@ time 구하기
ex)
SELECT CURRENT TIME
FROM SYSIBM.SYSDUMMY1
WITH UR
@@@ dayofyear 구하기(오늘이 365일중 몇번째 날짜)
ex)
SELECT DAYOFYEAR(CURRENT DATE)
FROM SYSIBM.SYSDUMMY1
WITH UR
@@@ dayname 구하기(요일 이름)
ex)
SELECT DAYNAME(CURRENT DATE)
FROM SYSIBM.SYSDUMMY1
WITH UR
@@@ CONCAT 문자연결함수
ex)
SELECT CONCAT('111','22222 ')
FROM SYSIBM.SYSDUMMY1
WITH UR
@@@ MOD 나머지 함수
ex)
SELECT MOD(11111,100)
FROM SYSIBM.SYSDUMMY1
WITH UR
@@@ value 함수 - COALESCE와 동일한 기능
ex)
SELECT VALUE(CURRENT DATE,'2004-08-16')
FROM SYSIBM.SYSDUMMY1
WITH UR
@@@ abs 함수 절대값 함수
ex)
SELECT ABS(-51234)
FROM SYSIBM.SYSDUMMY1
WITH UR
@@@ lcas 함수 대문자를 소문자로
ex)
SELECT LCASE('ABCD')
FROM SYSIBM.SYSDUMMY1
WITH UR;
@@@ ucase 함수 소문자를 대문자로
ex)
SELECT LCASE('abcd')
FROM SYSIBM.SYSDUMMY1
WITH UR;
@@@ multiply_alt 두 수를 곱한다.
ex)
SELECT MULTIPLY_ALT(3,20)
FROM SYSIBM.SYSDUMMY1
WITH UR;
@@@ round
ex)
SELECT ROUND(873.726, 2), ROUND(873.726, 1), ROUND(873.726, 0), ROUND(873.726,-1),
ROUND(873.726,-2), ROUND(873.726,-3), ROUND(873.726,-4)
FROM SYSIBM.SYSDUMMY1
WITH UR;
@@@ week_iso 함수
ex)
SELECT WEEK_ISO(CURRENT DATE) SELECT WEEK_ISO('1997-12-28')
FROM SYSIBM.SYSDUMMY1 FROM SYSIBM.SYSDUMMY1
WITH UR; WITH UR;
@@@ dayofweek_iso 해당주에서 몇일에 해당하는지
ex)
SELECT DAYOFWEEK_ISO(CURRENT DATE) SELECT DAYOFWEEK_ISO('2004-08-16')
FROM SYSIBM.SYSDUMMY1 FROM SYSIBM.SYSDUMMY1
WITH UR; WITH UR;
SELECT callback_dt,
callback_tm,
COUNT(seqno),
COUNT(custname),
telno_1||'-'|| telno_2||'-'|| telno_3 AS tel_number
FROM callback
GROUP BY callback_dt,callback_tm,telno_1||'-'|| telno_2||'-'|| telno_3
FETCH FIRST 5 ROWS ONLY;
SELECT * FROM
( SELECT ROWNUMBER() OVER() AS rownum,statement_text
FROM explain_statement
) AS t
WHERE t.rownum = 2
FETCH FIRST 100 ROWS ONLY
@@@ outer join
SELECT CASE WHEN a.relation = '1' THEN '본인'
WHEN a.relation = '2' THEN '배우자'
WHEN a.relation = '3' THEN '자녀'
WHEN a.relation = '4' THEN '부모'
WHEN a.relation = '5' THEN '형제자매'
WHEN a.relation = '6' THEN '기타'
ELSE '기타'
END AS kwan,a.name,a.fsocial_no AS fsocial_no,
CASE SUBSTR(a.fsocial_no,7,1)
WHEN '1' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
WHEN '2' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
WHEN '3' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
WHEN '4' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
WHEN '5' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
WHEN '6' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
WHEN '7' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
WHEN '8' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
WHEN '9' THEN YEAR(CURRENT DATE - DATE('18'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01'))
ELSE YEAR(CURRENT DATE - DATE('18'||SUBSTR(a.fsocial_no,1,2)||'-'||SUBSTR(a.fsocial_no,3,2)||'-'||'01'))
END AS YEARS,
VALUE(b.company_nm,'') AS COMPANY_NM,
VALUE(b.dept,'') AS DEPT,
VALUE(b.duty,'') AS DUTY,
VALUE(b.offi_tel_1,'') || VALUE(b.offi_tel_2,'') || VALUE(b.offi_tel_3,'') AS offi_tel,
CASE WHEN a.live_yn = '1' THEN '동거'
ELSE ''
END AS home
FROM cust.family_info A LEFT OUTER JOIN euc20.customer b ON ( a.fsocial_no = b.social_no )
WHERE a.social_no = '6611211010815'
ORDER BY fsocial_no
