[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 --이용기간
시작 시간 종료 시간 사이에 값 조회 .
[c#] 울트라 그리드 멀티 헤더
- </P><P>// Create data table
- DataTable dt = new DataTable();
- dt.Columns.Add("First Name");
- dt.Columns.Add("Second Name");
- dt.Columns.Add("Salary");
- dt.Columns.Add("Taxes");
- dt.Rows.Add("Joe","Smith",120000,1000);
- dt.Rows.Add("Mariah","Carry",1500000,10000);
- // Bind Datasource
- ultraGrid1.DataSource = dt;
- // Create Groups
- Infragistics.Win.UltraWinGrid.UltraGridGroup group1 = new Infragistics.Win.UltraWinGrid.UltraGridGroup( "Personal Info",1); // key + unique id
- Infragistics.Win.UltraWinGrid.UltraGridGroup group2 = new Infragistics.Win.UltraWinGrid.UltraGridGroup( "Salary Info",2);
- // Add Groups to BAND
- ultraGrid1.DisplayLayout.Bands[0].Groups.Add( group1 );
- ultraGrid1.DisplayLayout.Bands[0].Groups.Add( group2 );
- // *** Assign columns to groups **
- // option 1 Through gropus Columns
- group1.Columns.Add( ultraGrid1.DisplayLayout.Bands[0].Columns[0] );
- group1.Columns.Add( ultraGrid1.DisplayLayout.Bands[0].Columns[1] );
- group2.Columns.Add( ultraGrid1.DisplayLayout.Bands[0].Columns[2] );
- group2.Columns.Add( ultraGrid1.DisplayLayout.Bands[0].Columns[3] );
- // option 2 Columns group
- //ultraGrid1.DisplayLayout.Bands[0].Columns[0] .Group = group1;
- </P>
[c#] 확인 취소가 가능한 메세지 박스
DialogResult dr = MessageBox.Show("종료하시겠습니까?", "알림", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (dr == DialogResult.OK)
{
MessageBox.Show("정상적으로 종료되었습니다.");
}
else
{
MessageBox.Show("종료안한다고?");
}
[C#] 데이터 테이블 만들기.
DataTable dt = new DataTable();
dt.Columns.Add("INSERT_ST");
dt.Columns.Add("CUST_NUM");
dt.Columns.Add("RETURN_WHY");
GridHandler.FillGrid(gridList, dt);