BamTolLove

많이 사용하는 DB중에 하나가 오라클이지요. 여기서는 Oracle을 사용할 때 유용하게 사용될 스크립트에 대해서 알아보겠습니다. 1. 테이블에 특정컬럼에 중복된 값을 찾는 SQL /*--------------------------------------------------------------------------*/ /* USAGE : @중복찾기.SQL [테이블명] [중복을조사할컬럼명] /* /* WARNING : 똑같은값이 2개 이상있을때 처음값은 출력 않되고 2번째   /* 값부터 출력됨. < > /*--------------------------------------------------------------------------*/ SELECT * FROM &1 A WHERE ROWID >   (SELECT MIN(ROWID) FROM &1 B WHERE B.&2 = A.&2) ORDER BY &2; 2. PK와 FK간의 연관관계를 찾아 보여주는 SQL   /*--------------------------------------------------------------------------*/ /*　 사용법　　 　 :> @SHOW_POSITIONS　 PARENT_TABLE　 CHILD_TABLE　　   /*　 DESCRIPT-xION　 :　 SHOWS PRIMARY AND FOREIGN KEY POSITIONS　　   /*　　   /*　 WARNING　　 :　 이 문장은 해당 TABLE의 CONSTRAINT생성시 NAMING　　　   /*　　　　　　　　　 CONVENTION을 따른 경우에 적용되도록 되어 있다.　　   /*--------------------------------------------------------------------------*/ SET VERIFY OFF　　   CLEAR BREAK　　   BREAK ON CONSTRAINT_NA

메뉴의 구조라든지, 부서를 표현 할 때 트리로 보여주는 경우가 대부분인데요. 계층형 구조라고도 하지요. 이렇게 트리로 보여주는 요구는 엄청나게 많은데요. 이렇게 엄청나게 자주 사용하는 트리 구현을 어떻게 해야 할까요? 하여 오늘은 Tree 자바스크립트 구현(dTree)에 대해서 알아보겠습니다. dTree는 무료이며 순수 자바스크립트 만으로 구현이 가능 합니다. ▶ 메뉴 구현 아래의 소스를 이용하시면 쉽게 메뉴 구현을 하실수 있습니다. (부서도 같이 쉽게 구현이 가능 하십니다.) <div class="dtree"> <script type="text/javascript"> <!-- //id, pid, name, url, title, target, icon, iconOPne, open, d = new dTree('d'); d.config.target="right"; d.config.folderLinks = false; // ----------------- INTRODUCTION ------------------// d.add(0,-1,'<B>FusionCharts Free</B>','','FusionCharts Free Documentation'); d.add(1,0,'<a href="javascript:treeClick(1);">Introduction</a>','','Introduction & Installation','','','',true); d.add(101,1,'<a href="javascript:treeClick(2);"&g

DB를 설계할때 자주 사용하는 도구가 ERWin을 많이 사용하지요. 하지만, 저는 이번에 처음 사용해 보았네요...;;; 이번에 사용하면서 알게된 자주 사용할것 같은 기능들이 눈에 들어오네요...ㅎ 하여 오늘은 ERWin 자주 사용하는 기능에 대해서 알아볼까 합니다.  ▶ 네이밍 사전 등록 사용 칼럼들의 이름을 미리 등록하고 사전 처럼 언제든지 꺼내어 사용할 수 있는 기능입니다. 먼저 동그라미1번에서 처럼 "Tools" 탭을 클릭합니다. 그러면 동그라미2번과 같이 밑으로 메뉴들이 펼쳐지는데요. 2번의 "Names"를 클릭해서 오른쪽 메뉴의 3번 "Model naming Options..." 메뉴를 클릭하여  아래와 같이 Model naming Options 팝업을 띄워 주세요. 위와 같이 띄워진 Model naming Options 팝업창에서 동그라미4번과 같이 "Logical", "Physical" 탭의 기능을 이용해서 자주 사용하는 단어들을 등록하면 됩니다. 동그라미5번과 깉이 "Physical" 탭에서 알맞게 등록을 하시고 "Logical"에서도 동일한 방법으로 등록을 해줍니다. ▶ 기타 기능 - Domain Dictionary Domain Dictionary란 동일한 칼럼을 사용하는 여러개의 테이블에서 변경이 이루어질 경우 여러개의 테이블 정보에서 해당 칼럼을 수정하는것이 아닌 미리 등록해놓은 Domain에서 한번에 변경하여 사용 할 수 있도록 도와주는 기능 입니다. 동그라미6번의 "Model" 탭을 클릭하시고 "Domain Dictionary"를 클릭하셔서 해당 팝업창에서 Domain에 관한 정보를 등록하세요. 도메인이 등록되었다면 동그라미 8번, 9번, 10

자동으로 배치를 돌려서 통계를 생성하는 등의 작업에서 특정 테이블의 칼럼을 Alter Table 쿼리문을 사용하여 칼럼을 동적으로 생성해줘야하는 작업이 있습니다. 동적쿼리가 보안에 취약하여 많이 사용하지는 않지만, 그래도 종종 어쩔수 없이 사용하는 경우들이 있어 오늘은 MSSQL 테이블칼럼 동적 추가에 대해서 몇자 적어보겠습니다.  들어가기에 앞서 주의 사항이 있습니다. 주의 : 테이블변수에는 Alter Table 명령이 실행되지 않습니다. MSSQL에는 테이블에 대한 정의가 4가지 있습니다. 일반테이블, 전역 임시 테이블, 지역 임시 테이블, 테이블변수 이렇게 4가지가 있는데, 결론부터 말씀드리면 4가지 테이블 중에서 앞의 일반테이블, 전역 임시 테이블, 지역 임시 테이블 테이블에만 Alter Table 명령이 실행되고 나머지 하나인 테이블변수에는 실행되지 않습니다. ▶ 작업 쿼리 -- 임시 테이블 존재여부 체크 후 삭제 IF OBJECT_ID('tempdb..#TEST_TABLE', 'U') IS NOT NULL     DROP TABLE #TEST_TABLE;   DECLARE     @ColumnName01 VARCHAR(50)   , @ColumnName02 VARCHAR(50)   , @ColumnName03 VARCHAR(50)   , @ColumnName04 VARCHAR(50); SELECT     @ColumnName01 = '[Sales Person]'   , @ColumnName02 = '[' + DATENAME(MONTH, GETDATE()) + ' Sales]'   , @ColumnName03 = '[' + DATENAME(MONTH, DATEADD(MONTH, -1, GETDATE())) + ' Sales]'   , @ColumnName04

조금 생소한 제목 입니다. 많이 구현하지 않는 방식으로 이상한 현업의 이상한 요구 사항으로 만들어 봤는데 나중에 쓰임이 있지 않을까하고 몇자 적어 봅니다. 제목을 간단하게 풀어보면, From ~ To와 같이 시작일과 종료일 사이에 특정 요일에 해당하는 날짜들을 추출하는 쿼리를 구한다는 의미 입니다. 하여 오늘은 MSSQL 날짜 사이 특정 요일 날짜 구하기에 대해서 알아보도록 하겠습니다.  -- 먼저 From ~ To와 같이 시작일과 종료일 셋팅 합니다. DECLARE @FromDate VARCHAR(10) = '2019-06-01' DECLARE @ToDate VARCHAR(10) = '2019-12-31' DECLARE @FromDiff SMALLINT; DECLARE @ToDiff SMALLINT; DECLARE @MIN SMALLINT = 1; WHILE (@MIN <= 2) BEGIN   IF (CASE WHEN @MIN = 1 THEN @DATEPART_FROM ELSE @DATEPART_TO END = 1) -- 일요일    IF (@MIN = 1) SET @FromDiff = 4 ELSE SET @ToDiff = 4;   ELSE IF (CASE WHEN @MIN = 1 THEN @DATEPART_FROM ELSE @DATEPART_TO END = 2) -- 월요일    IF (@MIN = 1) SET @FromDiff = 3 ELSE SET @ToDiff = 3;   ELSE IF (CASE WHEN @MIN = 1 THEN @DATEPART_FROM ELSE @DATEPART_TO END = 3) -- 화요일    IF (@MIN = 1) SET @FromDiff = 2 ELSE SET @ToDiff = 2;   ELSE IF (CASE WHEN @MIN = 1 THEN @DATEPART_FROM ELSE @DATEPART_TO END = 4) --

얼마전에 십단위 올림에 대해서 알아보다가 반올림, 올림, 버림에 대해서 여러가지 시스템 함수를 이용하는 방법에 대해서 알아보았었는데요.(아래 링크) [MSSQL 로우합치지 및 십단위 올림 참고] 여기서 조금 더 자세하게 알아보고 날짜에 대한것도 자세히 알아보도록 하겠습니다. 하여 MSSQL 올림, 반올림, 절삭 및 날짜 포맷에 대하여 아래와 같이 준비 했습니다. ▶ 반올림 및 자르기 1. 함수 : ROUND ( numeric_expression, length [, function]) 사용 DECLARE @AAA DECIMAL(16,5) = 12.35393 SELECT ROUND(@AAA,2)   => 결과값 : 12.35000   SELECT ROUND(@AAA,2)   => 결과값 : 12.35000 SELECT ROUND(@AAA,-1)   => 결과값 : 10.00000 SELECT ROUND(@AAA,-1)   => 결과값 : 10.00000 SELECT ROUND(@AAA,0)   => 결과값 : 12.00000 2. 자르기 : ROUND ( numeric_expression, length [, function]) 사용 SELECT ROUND(@AAA, 1, 1)  => 결과값 : 12.30000 SELECT ROUND(@AAA, 0, 1)  => 결과값 : 12.00000 SELECT ROUND(@AAA, -1, 1)  => 결과값 : 10.00000 ▶ 올림 함수 : CEILING ( numeric_expression ) 사용 SELECT CEILING(@AAA) => 결과값 : 13  SELECT CEILING( -@AAA ) => 결과값 : -12  SELECT CEILING(0.00)   => 결과값 : 0 ▶ 내림 함수 : FLOOR ( nume

프로젝트에서 개발을 하다보면 Row로 구성된 데이터를 한줄의 문장으로 보여달라는 요구사항이 종종 나옵니다. 이럴때는 어떻게 해야 할까요? 또한, 통계 데이터를 만지다보면 십단위로 올림을 줘야 하는 경우가 있는데 이것은 어떻게 해야 할까요? 하여 오늘은 MSSQL 로우합치지 및 십단위 올림에 관해서 알아보겠습니다.  ▶ Row 합치기 -- 테스트를 위한 테이블 변수를 생성 DECLARE @TABLE TABLE (  ID  INT  , Title VARCHAR(10) ) -- 테이블변수에 데이터 생성 INSERT INTO @TABLE (ID, Title) VALUES (11, '가1') INSERT INTO @TABLE (ID, Title) VALUES (22, '나2') INSERT INTO @TABLE (ID, Title) VALUES (23, '다3') INSERT INTO @TABLE (ID, Title) VALUES (33, '라4') INSERT INTO @TABLE (ID, Title) VALUES (33, '마5') INSERT INTO @TABLE (ID, Title) VALUES (33, '바6') INSERT INTO @TABLE (ID, Title) VALUES (44, '사7') INSERT INTO @TABLE (ID, Title) VALUES (44, '아8') INSERT INTO @TABLE (ID, Title) VALUES (44, '자9') INSERT INTO @TABLE (ID, Title) VALUES (44, '차10') -- 최종 로우 합치기 쿼리 SELECT  DISTINCT ID  ,   STUFF((     SELECT      ',' + Title     FROM @T

통계 화면을 하다보면 전주의 정보를 보고 싶다. 전전주의 정보를 보고 싶다. 금주의 정보를 보고 싶다. 등등의 많은 요청 사항들이 있습니다. 이런 요청 사항은 어떻게 처리해야 할까요? 하여 여기서는 MSSQL 전주 구하기 및 칼럼 정보 합치기에 대해서 알아보겠습니다. 또한, Row로 구성된 칼럼 값들을 합쳐서 하나의 문자열로 나타내는 참고 쿼리도 알아보겠습니다.  ▶ 전주 구하기 먼저 오늘 기준 전전전주 일요일, 전전주 일요일, 전주 일요일, 해당주 일요일을 구하는 쿼리 입니다. SELECT CONVERT(VARCHAR(10), DATEADD(wk, DATEDIFF(d, 0, GETDATE()) / 7 , -15), 120)  -- 전전전주 일요일 SELECT CONVERT(VARCHAR(10), DATEADD(wk, DATEDIFF(d, 0, GETDATE()) / 7 , -8), 120)  -- 전전주 일요일 SELECT CONVERT(VARCHAR(10), DATEADD(wk, DATEDIFF(d, 0, GETDATE()) / 7 , -1), 120)  -- 전주 일요일 SELECT CONVERT(VARCHAR(10), DATEADD(wk, DATEDIFF(d, 0, GETDATE()) / 7 , 6), 120)  -- 해당주 일요일 위의 쿼리를 참고하면 많은 응용이 가능 합니다. ▶ Row 칼럼 값들 합치기 다음으로 Row로 구성된 칼럼 값들을 합쳐서 하나의 문자열을 만드는 쿼리 입니다. STUFF라는 예약어를 활용하여 한줄의 문자열로 표시 합니다. -- 임시 테이블 생성 DECLARE @TABLE TABLE (  ID  INT  , Title VARCHAR(10) ) -- 테스트 값 입력 INSERT INTO @TABLE (ID, Title) VALUES (11, '가1') INSERT INTO @TABLE (ID, Title)

프로젝트나 개발을 하다보면 부서 정보와 같은 계층형 정보를 트리로 보여줘야 하는 작업을 많이하게 됩니다. 이와 관련하여 아래의 글에서 알아보았었습니다. [MSSQL 계층형(Hierarchy) 및 숫자를 한글로 변환] 지난 글에서 더 나아가 사용자도 같이 보여줘야 하는 경우는 어떻게 할까요? 하여 오늘은 MSSQL 계층형 부서 와 사용자 트리 쿼리에 대해서 알아보겠습니다. 아래의 쿼리를 참고하시면 쉽게 작업이 가능하실거에요...ㅎ  ;WITH TREECTE(DEPTID, PDEPTID, DEPTNM, SORT, [Level], GUBUN)  AS  (   -- 최상위 부서   SELECT    DEPTID    , ISNULL(PDEPTID, 0) AS PDEPTID    , DEPTNM    , CONVERT(VARCHAR, DEPTID) AS SORT    , 1 AS [Level]    , 'D' AS GUBUN   FROM Tbl_DEPT   WHERE PDEPTID IS NULL      UNION ALL     -- 최상위 부서 밑의 하위 부서   SELECT    C.DEPTID    , ISNULL(C.PDEPTID, 0) AS PDEPTID    , C.DEPTNM    , CONVERT(VARCHAR, P.SORT + ':' + CONVERT(VARCHAR, C.DEPTID)) AS SORT    , P.[Level] + 1 AS [Level]    , 'D' AS GUBUN   FROM Tbl_DEPT AS C    INNER JOIN TREECTE AS P ON C.PDEPTID = P.DEPTID   UNION ALL   -- 각 부서에 매핑되있는 사용자   SELECT    CU.CUSRUID AS DEPTID    , ISNULL(C.PDEPTID, 0) AS PDEPTID    , CU.CUS

프로젝트를 하다보면 아주 빈번하게 하게되는 작업이 바로 통계 장표를 만드는 일일덴데요. 통계 화면에서는 제품별, 날짜별 등의 특정 칼럼에 대한 합을 많이 표현하지요. 하여 오늘은 MSSQL 소계, 총합 구하기에 대해서 알아보겠습니다. (컨트롤에 Trigger 등의 이벤트를 생성해서 click이벤트를 걸우준 경우는 ie9 이하에서는 에러를 발생 합니다.) 아래의 쿼리를 참고하시면 되겠습니다. -- 테이블 변수를 생성합니다. DECLARE @T_STATIC TABLE (     TREETITLE        VARCHAR(20),     TERMNAME        VARCHAR(20),     TERMOFFTYPENAME    VARCHAR(20),     TERMSTARTDATE    SMALLDATETIME,     TERMENDDATE        SMALLDATETIME,     COMPANYNAME    VARCHAR(20),     USERNAME        VARCHAR(20) ) -- 테스트 할 데이터를 등록 합니다. INSERT INTO @Table VALUES ('treetitle1','termname2','termofftypename','2019-02-21','2007-02-23','companyname1','username1') INSERT INTO @Table VALUES ('treetitle1','termname1','termofftypename','2019-02-22','2007-02-23','companyname1','username2') INSERT INTO @Table VALUES ('treetitle1','termname3','termo

전에는 행을 열로 바꾸고 열을 행으로 바꾸는 변환에 대해서 알아보았습니다. 아래 링크를 걸어 놓겠습니다. [MSSQL Column을 Row로 Row를 Column로 변환 (Pivot, Unpivot)] 보시면 아시겠지만 Pivot을 사용하여 얻은 결과 값에 as를 사용하여 알리아스(Alias)를 사용할 수 없습니다. 간혹이긴 하지만 프로젝트를 하다보면 Pivot을 사용하면서 칼럼의 이름을 바꿔줘야 하는 경우가 있어 오늘은 MSSQL Pivot 칼럼 이름 바꾸기에 대해서 알아보겠습니다.  아래 쿼리를 참고로 하시면 됩니다. SELECT   PVT.Top_DeptNm   , PVT.GUBUN   , PVT.Code   , Target_AddQ = PVT.[Add+Q]   , Target_1 = PVT.[1]   , Target_2 = PVT.[2]   , Target_3 = PVT.[3]   , Target_4 = PVT.[4]  FROM (    SELECT     A.Top_DeptNm     , '매출' AS GUBUN     , A.Code     , A.QuarterAlias     , SUM(B.TargetMoney) AS TargetMoney    FROM @BASIC_QUARTERALIAS A     LEFT OUTER JOIN (          SELECT           A.*, dbo.fnGetTopDepNm__C013(U.DeptId) AS DeptNm          FROM @TABLE_MONTH A           LEFT OUTER JOIN Users U ON A.SalesUserId = U.UserId          WHERE CUST01 = '10'         ) B ON A.Top_DeptNm = B.DeptNm AND A.Code = B.CUST04 AND A.QuarterAlias = B.Qu

빈번하게 많이 생기는 일은 아니지만 가끔 통계자료를 추출 할 경우 칼럼을 로우 형태로 작업해야하고 또는 로우를 칼럼의 형태로 작업해서 장표를 만들어야 하는 경우가 종종 있습니다. 이럴때 사용하는 것이 MSSQL에서 제공하는 Pivot와 Unpivot 입니다. 하여 오늘은 MSSQL Column을 Row로 Row를 Column로 변환 (Pivot, Unpivot)에 대해서 알아보도록 하겠습니다. ▶ Row를 Column으로 변환 (PIVOT) SELECT 컬럼명 FROM 테이블명 PIVOT ([집계함수] ([집계 컬럼명]) FOR [대상 컬럼명] IN ([컬럼이 될 값])) AS 피벗테이블명 => 집계 함수 : SUM(합계), AVG(평균), COUNT(개수), MAX(최대), MIN(최소) 예) DECLARE @SUBJECT VARCHAR(100); WITH PIVOT_TABLE AS (  SELECT '1반' AS 반정보, '국어' AS 과목, 90 AS 점수  UNION ALL  SELECT '1반' AS 반정보, '수학' AS 과목, 80 AS 점수  UNION ALL  SELECT '2반' AS 반정보, '국어' AS 과목, 70 AS 점수  UNION ALL  SELECT '2반' AS 반정보, '수학' AS 과목, 60 AS 점수  UNION ALL  SELECT '3반' AS 반정보, '영어' AS 과목, 50 AS 점수 ) SELECT * FROM PIVOT_TABLE PIVOT (SUM(점수) FOR 과목 IN ([국어], [수학], [영어])) AS PVT ▶ Column를 Row으로 변환 (UNPIVOT) SELECT 컬럼명 FROM 테이블명 UNPIVOT ([집계될 데이터의 컬럼명] FOR

MSSQL 뿐만 아니라 프로젝트를 하다보면 계층형(Hierarchy) 구조의 설계를 많이 봅니다. 가장 많이 접하는 대표적인 예가 부서 정보일거라고 생각되네요. 하여 오늘은 빈번하게 사용하는 MSSQL 계층형(Hierarchy) 및 숫자를 한글로 변환에 대해서 알아보겠습니다.  ▶ 계층형(Hierarchy) 구조 아래 쿼리는 부서에 관한 정보를 계층형으로 추출하는 쿼리 입니다. WITH Department_ (DeptId, DeptNm, Sort, ParentDeptId, Hierarchy) AS (  SELECT   DeptId, DeptNm, Sort, DeptId   ,CONVERT(VARCHAR, DeptId) AS Hierarchy  FROM Department WITH(NOLOCK)  WHERE Depth = 0 -- 최상위 정보  UNION ALL  SELECT   A.DeptId, A.DeptNm, A.Sort, A.ParentDeptId   ,CONVERT(VARCHAR, Hierarchy + ':' + CONVERT(VARCHAR, A.DeptId)) AS Hierarchy  FROM Department A WITH(NOLOCK)   INNER JOIN Department_ B ON A.ParentDeptId = B.DeptId ) 상기의 쿼리를 이용하면 어떠한 계층형도 다~ 뽑아집니다.ㅎ ▶  숫자를 한글로 변환해주는 함수 ALTER FUNCTION [dbo].[fnMoneyString] (  @WON BIGINT ) RETURNS VARCHAR(200) AS BEGIN  DECLARE  @INPUT_VALUE VARCHAR(50)  , @NOW_POSITION TINYINT  , @ACC_KOREAN_VALUE NVARCHAR(100)  , @NOW_POSITION_VALUE CHAR(1)  , @