[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 --이용기간
시작 시간 종료 시간 사이에 값 조회 .
[SQL] 전체 조회 조건조회 하기
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 :ROUNGE_ST AND :ROUNGE_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');
[c#] 이미지 파일 열기
// 이미지파일을여는것이다
// 포멧은파일을우선으로했으나다른모든파일도검색이될수있게
// 구현하였습니다
OpenFileDialog openFile = new OpenFileDialog();
openFile.DefaultExt = "jpg"
openFile.Filter = "Graphics interchange Format (*.jpg)|*.jpg|All files(*.*)|*.*"
openFile.ShowDialog();
// 만약파일이선택되었으면
if (openFile.FileName.Length > 0)
{
// 피쳐박스에뿌려주기
Image image = Image.FromFile(openFile.FileName);
// Image 클래스뿐아니라위에서상속된클래스로도
// 이미지를보여줄수있다
Bitmap image = new Bitmap(openFile.FileName);
//Image image = Image.FromStream(stream);
// 여기서은이미지정보가포함된스트림이다
picbox_main.Image = image;
}
셀렉트 테이블 다 열기.
select * from table명
select a,b,c,d from table명
]
c# 버튼 활성화 비활성화
버튼 변수.Enabled = false; //비활성
버튼 변수.Enabled = true; //활성
배경 이미지 바꾸기.
컨트롤 이름.BackgroundImage = 이미지 경로;
예시 btn_DM.BackgroundImage = Properties.Resources.btn_dm;
[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