[TeraData] 커서 2개 열고 각각 조회,.
REPLACE PROCEDURE LDVIDB.UP_ROUNGE_USE_CUST
(
IN ST_CD CHAR(4) --관리점코드 --원 소스 ST_CD
, IN LOUNGE_CD CHAR(2) --라운지코드
, IN FAMY_GB CHAR(1) --가족구분
, IN LOUNGE_ST CHAR(8) --라운지이용기간 - 시작
, IN LOUNGE_ET CHAR(8) --라운지이용기간 - 종료
)
/*
수정일: 2014-1-27
작성자: 김우종
시스템명: VIP
업무명: 마이포지션
화면ID: RG2300M
메뉴: 마이포지션 -> 우수고객서비사관리 -> 라운지이용내역조회
SP설명: 라운지 이용내역 조회
SP실행: CALL LDVIDB.UP_ROUNGE_USE_CUST('0001' , '1111', '1', '20111111' , '20141212');
*/
DYNAMIC RESULT SETS 2
BEGIN
BEGIN
DECLARE cur1 CURSOR WITH RETURN ONLY FOR
LOCKING ROW FOR ACCESS
SELECT
SUBSTR(B.USE_DT,1, 4) || '-' || SUBSTR(B.USE_DT, 5,2) || '-' || SUBSTR(B.USE_DT, 7,2)
AS USE_DT--이용일자
, SUBSTR(B.ETR_TM, 1, 2) || ':' || SUBSTR(B.ETR_TM, 3, 2)
AS ETR_TM--입실시간
, A.KOR_NM AS CUST_NM --고객이름
, A.CUST_NO AS CUST_NUM --고객번호
--, A.RGST_NO AS RGST_NO
, (CASE A.FAMY_GB WHEN '1' THEN '본인'
WHEN '2' THEN '가족'
ELSE '' END) AS ME_FAMILY --가족구분
,(SELECT sel_lvl_nm
FROM LDVMDB.VI_SEL_LVL_CD_V
WHERE sel_flg_cd = '1'
AND use_yn = 'Y'
AND sel_lvl_cd = A.SEL_LVL_CD) AS RNK_NM --등급
, (SELECT b_code_nm
FROM LDVMDB.VI_BASE_CODE_V
WHERE p_code ='MY016'
AND b_code = A.INNER_GRD) AS INNER_RNK_NM --내부등급
,CASE B.MVG_ROOM_ST_CD WHEN '9999' THEN '전점'
ELSE COALESCE ((SELECT store_nm
FROM LDVMDB.PO_ST_V
WHERE store_cd = B.MVG_ROOM_ST_CD),'') END ST_CD --이용점
FROM LDVMDB.VI_MVG_CUST_V AS A
INNER JOIN LDVMDB.VI_MVG_ROOM_V AS B
ON A.CUST_NO = B.INTG_CUST_ID
WHERE B.USE_DT BETWEEN :LOUNGE_ST AND :LOUNGE_ET --이용기간
AND (ST_CD = :ST_CD OR :ST_CD = '9999')
AND (B.LOUNGE_CD = :LOUNGE_CD OR :LOUNGE_CD= '99') --라운지코드
AND (A.FAMY_GB = :FAMY_GB OR :FAMY_GB = '0')
ORDER BY B.USE_DT DESC;
OPEN cur1;
END;
BEGIN
DECLARE cur2 CURSOR WITH RETURN ONLY FOR
LOCKING ROW FOR ACCESS
SELECT COUNT(A.CUST_NO) AS CNT
, SUM(B.ACCMPNY_CNT) AS CNT2 --동반고객수
FROM LDVMDB.VI_MVG_CUST_V AS A
INNER JOIN LDVMDB.VI_MVG_ROOM_V AS B
ON A.CUST_NO = B.INTG_CUST_ID
WHERE B.USE_DT BETWEEN :LOUNGE_ST AND :LOUNGE_ET --이용기간
AND (ST_CD = :ST_CD OR :ST_CD = '9999')
AND B.LOUNGE_CD = :LOUNGE_CD OR :LOUNGE_CD= '99' --라운지코드
AND (A.FAMY_GB = :FAMY_GB OR :FAMY_GB = '0');
OPEN CUR2;
END;
END;
[TeraData] 한가지 커서로 여러 조건 적용.
REPLACE PROCEDURE LDVIDB.UP_NOTICE_GETNOTICELIST --Notice_GetNoticeList
(
IN SearchOp VARCHAR(7)
,IN SearchText VARCHAR(1000)
)
/*
수정일: 2014-1-10
작성자: 김우종
시스템명: VIP
업무명: 시스템관리
화면ID: CM0100M
메뉴: 시스템관리 -> 공지사항 관리 -> 공지사항
SP설명: 권한 코드
SP실행: CALL LDVIDB.UP_NOTICE_GETNOTICELIST('ALL','명동점');
*/
DYNAMIC RESULT SETS 1
BEGIN
DECLARE STRSQL VARCHAR(8000);
SET STRSQL = ' ';
IF SearchOp = 'ALL' THEN-- 전체
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
,Ntitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE use_yn = ''Y''
ORDER BY notice_id DESC; ';
END IF;
IF SearchOp = 'TITLE' THEN-- 제목
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
, NTitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE
use_yn = ''Y'' AND NTitle LIKE ''%' || SearchText || '%''
ORDER BY notice_id DESC;';
END IF;
IF SearchOp = 'CONTE' THEN-- 내용
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
, NTitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE
use_yn = ''Y'' AND contents LIKE ''%' || SearchText || '%''
ORDER BY notice_id DESC;';
END IF;
IF SearchOp = 'USER' THEN -- 사용자
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
, NTitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE
use_yn = ''Y'' AND usr_id LIKE ''%' || SearchText || '%''
ORDER BY notice_id DESC;';
END IF;
IF SearchOp = 'Total' THEN -- 전체 공지만 조회
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
, NTitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE
use_yn = ''Y'' AND usr_id LIKE ''%' || SearchText || '%''
AND sys_cd = 00
ORDER BY notice_id DESC;';
END IF;
IF SearchOp = 'Sys' THEN -- 시스템 공지만 조회
SET STRSQL =
'SELECT
notice_id
,sys_cd
, CASE a.sys_cd WHEN ''00'' THEN ''전체''
ELSE COALESCE ((SELECT b_code_nm FROM LDVMDB.VI_BASE_CODE_V WHERE p_code = ''CM006'' AND use_yn = ''Y'' AND b_code = a.sys_cd), '''') END sys_nm
, NTitle
, usr_id
, SUBSTR(startdt,1, 4) || ''-'' || SUBSTR(startdt, 5,2) || ''-'' || SUBSTR(startdt, 7,2) || '' ~ '' ||
SUBSTR(enddt,1, 4) || ''-'' || SUBSTR(enddt, 5,2) || ''-'' || SUBSTR(enddt, 7,2) as term
FROM LDVMDB.VI_NOTICE_V a
WHERE
use_yn = ''Y''
AND usr_id LIKE ''%' || SearchText || '%''
AND sys_cd <> ''00''
ORDER BY notice_id DESC;';
END IF;
BEGIN
DECLARE CUR1 CURSOR WITH RETURN ONLY TO CLIENT FOR SQL_STATEMENT;
PREPARE SQL_STATEMENT FROM STRSQL;
OPEN CUR1;
END;
END;
[TeraData] 시간 내 조건 조회.
WHERE B.USE_DT BETWEEN :LOUNGE_ST AND :LOUNGE_ET --이용기간
시작 시간 종료 시간 사이에 값 조회 .
[TeraDate] Random 함수
[TeraData] Extract 추출
EXTRACT : 추출
EXTRACT([YEAR|MONTH|DAY|HOUR|MINUTE|SECOND] FROM date-value)
보면 알겠지만 date-value에서 원하는 부분을 추출한다.
예)
EXTRACT(YEAR FROM DATE'2010-12-20' + 30) = 2011
EXTRACT(MONTH FROM DATE'2010-12-20' - 30) = 11
EXTRACT(DAY FROM DATE'2010-12-20' + 30) = 19
EXTRACT(HOUR FROM TIME'10:20:30') = 10
EXTRACT(MINUTE FROM TIME'10:20:30') = 20
EXTRACT(SECOND FROM TIME'10:20:30') = 30
연, 달, 일은 DATE, 시, 분, 초는 TIME
[출처] [Teradata] EXTRACT|작성자 Ehne
[TeraData] Sample 함수
오라클의 샘플함수와 사용방법이 다르다.
SAMPLE {n[, n2, n3...] | d[, d2, d3...]}
건수와 비율로 설정할 수 있는데
정수일 경우 건수, 소수(0~1)일 경우 비율건수다.
예)
SELECT * FROM table SAMPLE 10;
=> 10건의 행을 랜덤하게 추출한다.
SELECT * FROM table SAMPLE 0.1;
=> 10%건의 행을 랜덤하게 추출한다. 0.1대신 .1로 써도 된다.
=> %결과가 소수점이 나오는 경우 .4999 이상이면 올림된다. => 반올림 된다는 말.
SAMPLEID라는 키워드를 사용할 수 있다.
예) SELECT cols, SAMPLEID FROM table, SAMPLE 2, 3, 1 ORDER BY SAMPLEID;
결과)
cols SAMPLEID
---- ---------
a 1
b 1
ab 2
c 2
abb 2
ccc 3
총 행 수 보다 SAMPLE에서 뽑아내는 행 수가 더 많으면 경고가 발생한다.
Warning: 7473 Requested sample is larger than table row. All rows returned.
소수 값의 합이 1을 넘거나 정수와 소수를 섞어 사용하면 실패한다.
Failure 5473 SAMPLE clause has invalid set of arguments.
SAMPLE절에 WITH REPLACEMENT를 함께 쓰면 결과에 중복값도 포함된다. (확실치 않음)
예) SELECT cols FROM table SAMPLE WITH REPLACEMENT 5;
결과)
cols
----
a
a
b
ab
b
다음과 같은 경우에는 의도와는 다른 결과가 나온다.
SELECT SUM(col) FROM table SAMPLE 10; // 10개의 샘플을 뽑아 더할 것을 의도했다.
그러나 위의 쿼리는 전체 테이블의 SUM값이 나온다.
Warning: 7473 Requested sample is larger than table rows. All rows returned
그럼 어떻게 처리해야할까?
SELECT SUM(col) FROM (SELECT col FROM table SAMPLE 10);
위와 같이 10개의 결과를 가지는 서브쿼리(테이블)를 만들어 SUM시키면 된다.
이렇게도 쓸수있다. RANDOMIZED ALLOCATION - 무슨 기능인지 몰라서 설명은 패스...
SAMPLE WITH REPLACEMENT RANDOMIZED ALLOCATION 4, 5
그룹별 샘플링
예)
SELECT * FROM table
SAMPLE
WHEN col BETWEEN 0 AND 10 THEN 0.2
WHEN col BETWEEN 10 AND 100 THEN .4
WHEN col BETWEEN 100 AND 1000 THEN 0.25, 0.1
END
ORDER BY col;
=> col이 0~10인 그룹에서 20%건, 10~100인 그룹에서 40%건, 100~1000인 그룹에서 25%, 10%건을 추출한다.
예2)
SELECT * FROM table
SAMPLE WITH REPLACEMENT
WHEN col < 100 THEN 10
WHEN col < 200 THEN 5, 5
ELSE 1, 2, 3
END;
[출처] [Teradata] SAMPLE 함수|작성자 Ehne
[TeraData] CAST 함수 (데이터 타입 변환)
다른 DB환경에서도 사용 가능한 것 같지만 일단 Teradata 교제에서 나오는 내용이니 Teradata로 분류
Char -> DATE
CAST('20100101' AS DATE FORMAT 'YYYYMMDD')
=> 20100101 // DATE형 - 출력 포맷은 툴에 따라 다를 수 있음.
Char -> Numeric
CAST('1234' AS DECIMAL(4, 2) FORMAT '9(2)V9(2)');
=> 12.34 // Numeric Overflow 주의. 가능한 명확한 포맷을 지정
Char -> TIME
CAST('01:00:00' AS TIME(0))
=> 01:00:00 // TIME(n)에서 n은 초에 대한 정밀도이다. 따라서 주어진 Char의 초의 정밀도가 n 이하여야 한다.
Char -> TIMESTAMP
CAST('2010-01-01 01:01:01.12345' AS TIMESTAMP(5));
=> 2010-01-01 01:01:01.12345 // TIMESTAMP(n)에서 n은 TIME과 마찬가지다.
DATE -> Char
CAST(CURRENT_DATE AS CHAR(10))
=> 2013-04-10
CAST(CAST(CURRENT_DATE AS FORMAT 'YYYY/MM/DD') AS CHAR(10)); // 결과 포맷을 다르게 하기 위해서 AS FORMAT을 추가
=> 2012/03/29
DATE -> TIMESTAMP
CAST(DATE '2010-01-01' AS TIMESTAMP(1));
=> 2010-01-01 00:00:00.0
Numeric -> Char
CAST(12345 AS CHAR(5))
=> 12345 // 명시적으로 CAST를 사용하지 않을 경우 일부 데이터에 대해서 truncation이 발생할 수 있으므로 주의
Numeric -> Numeric
CAST((A * B) / C AS DECIMAL(15, 2)); // A, B, C가 DEC(8, 2)일 때 결과값을 DEC(15, 2)로 변환하는 경우
TIMESTAMP -> Char
CAST(CURRENT_TIMESTAMP AS CHAR(26));
=> 2013-04-10-18.25.31.210000
CAST(CAST(CURRENT_TIMESTAMP AS FORMAT 'YYYY/MM/DDBHH:MI:SS.S(6)') AS CHAR(26));
=> 2013-04-10 18:25:31.210000 // 결과 포맷을 다르게 하기 위해서 AS FORMAT을 추가
TIMESTAMP -> DATE
CAST(CURRENT_TIMESTAMP AS DATE);
=> 2013-04-10
CAST(TIMESTAMP '2010-01-01 01:02:03' AS DATE);
=> 2010-01-01
[출처] [Teradata] CAST 함수 (데이터 타입 변환)|작성자 Ehne
[SQL] NULL 관련 함수
NVL(c, v) : c가 NULL이 아니면 NULL, NULL이면 v를 반환.
NVL2(c, v1, v2) : [Oracle] c가 NULL이 아니면 v1, NULL이면 v2를 반환.
NULLIF(c1, c2) : c1 <> c2 이면 NULL, c1 = c2 이면 c1을 반환.
COALESCE(c1 [, c2, c3, ...], v) : c1이 NULL이 아니면 c1. c1이 NULL이면서 c2가 NULL이 아니면 c2. c2도 NULL이면 c3...
결국 끝까지 NULL이면 v를 반환.
[출처] [SQL] NULL 관련 함수|작성자 Ehne
[TeraData] IN, EXISTS 제공자와 확인자
IN은 제공자 EXISTS는 확인자 역할을 한다.
IN은 먼저 서브쿼리를 분석해와서 메인쿼리에게 비교하기 위한 값들을 제공하고
EXISTS는 단지 존재하는지만 확인해주는 역할을 한다.
IN과 EXISTS의 가장 큰 차이는 찾으려는 값에 NULL이 있을 때다.
IN은 다음과 같이 동작한다. subquery1의 반환값이 'A', 'B', 'C'라고 가정
'A' IN (subquery1)
=> 'A' IN ('A', 'B', 'C')
=> 'A' = 'A' OR 'A' = 'B' OR 'A' = 'C'
=> true OR false OR false
=> true
따라서 NOT IN은
'A' NOT IN (subquery1)
=> 'A' NOT IN ('A', 'B', 'C')
=> 'A' <> 'A' AND 'A' <> 'B' AND 'A' <> 'C'
=> false AND true AND true
=> false
이번에는 subquery2의 반환값에 NULL이 있다고 가정
'A' IN (subquery2)
=> 'A' IN ('A', 'B', NULL)
=> 'A' = 'A' OR 'A' = 'B' OR 'A' = NULL
=> true OR false OR ?
=> ? // 결과적으로는 false와 같아진다.
그렇다면 NOT IN은
'A' NOT IN (subquery2)
=> 'A' NOT IN ('A', 'B', NULL)
=> 'A' <> 'A' AND 'A' <> 'B' AND 'A' <> NULL
=> false AND true AND ?
=> ? // NULL이 포함된 결과와 IN, NOT IN을 한다면 둘다 ? 이므로 false나 마찬가지.
즉, false는 아니지만... NULL이 있을 때는 IN 과 NOT IN의 결과가 같다.
중요한 점은. NULL값이 있으면 서브쿼리가지고 뭔가를 하려고 했던 메인쿼리에서 아무것도 할 수가 없다.
다음은 EXISTS. subquery3에서 조건에 의해 찾은 값이 있다고 가정 (찾은 값이 NULL이더라도)
subquery3 AS (SELECT NULL FROM table WHERE cmp) // 조건 cmp에 의해 찾아도 결과는 항상 NULL이다. 그러나 찾았다는게 중요
EXISTS (subquery3)
=> EXISTS (찾았다!)
=> true
따라서 NOT EXISTS는
NOT EXISTS (subquery3)
=> NOT EXISTS (찾았다!)
=> false
즉, 조건만 맞으면 결과가 NULL이든 뭐든 true or false로 나눠진다.
추가적으로
IN을 쓸 때는 서브쿼리 범위가 작아야 유리하다. 서브쿼리 전체를 뒤지기 때문(?)
EXIEST를 쓸 때는 메인쿼리 범위가 작아야 유리. 서브쿼리는 있는지만 확인 (찾을 때까지만 돌고 있으면 끝남)
IN을 사용할 때는 NULL값이 있는지 확인하고 처리 해줘야 한다.
[SQL] NULL 관련 함수 참고
[출처] [SQL] IN과 EXISTS (NOT IN과 NOT EXISTS)|작성자 Ehne
[TeraData] 중복 가능한 테이블 생성하기 (MULTISET)
Teradata에서 테이블을 만들 때 기본적으로 중복값을 허용하지 않게 만들어진다.
SET / MULTISET이 중복 허용을 설정하는 키워드다.
MULTISET을 지정하면 테이블의 중복행을 허용한다. (즉 SET이 기본값이다.)
예)
CREATE MULTISET TABLE table
(
...
)
UNIQUE PRIMARY INDEX(col1, col2);
반대로 SET을 지정하면 테이블의 중복행을 허용하지 않는다.
기본값이므로 생략해도 된다.
[출처] [Teradata] SET / MULTISET|작성자 Ehne