테이블 이관 후 건수 및 값 검증

--ORACLE
SELECT 'TEST_TABLE' AS TBLID
, COUNT(*) AS CNT
, 0
+ NVL(SUM(LENGTH(TRIM(COL_CHR))),0)
+ NVL(ROUND(SUM(COL_NUM)),0)
+ NVL(ROUND(SUM(TO_DATE(TO_CHAR(COL_DAT,'YYYYMMDD'),'YYYYMMDD') - TO_DATE('20000101','YYYYMMDD'))),0)
+ NVL(SUM(LENGTH(CAST(DBMS_LOB.SUBSTR(COL_CLOB,2000,1) AS NVARCHAR2(2000)))),0)
AS CHK_SUM
FROM TEST_TABLE
;

* 오라클내에서 char 값까지 검증할 경우 ORA_HASH 함수 이용


--MSSQL
SELECT 'TEST_TABLE' AS TBLID
, COUNT(*) AS CNT
, 0
+ ISNULL(SUM(CAST(LEN(LTRIM(COL_CHR)) AS NUMERIC)),0)
+ ISNULL(SUM(COL_NUM),0)
+ ISNULL(SUM(CAST(DATEDIFF(DAY, CONVERT(DATE, '20000101', 112), CONVERT(DATE, CONVERT(VARCHAR(8), COL_DAT, 112), 112)) AS NUMERIC)),0)
+ ISNULL(SUM(CAST(LEN(REPLACE(CAST(COL_CLOB AS NVARCHAR(2000)),' ','.')) AS NUMERIC)),0)
AS CHK_SUM
FROM TEST_TABLE
;