CREATE COLUMN TABLE "SCHEMA_Q"."DATA_QUALITY_CHECK_RULES" ("DQERR_ID" INTEGER CS_INT,
"TABLE_NAME" VARCHAR(100),
"COLUMN_NAME" VARCHAR(100),
"VALIDATION_RULE" NVARCHAR(1000),
"VALIDATION_RULE_DESC" VARCHAR(100),
"ERROR_CODE" VARCHAR(500),
"ACTIVE_KEY" VARCHAR(1)) UNLOAD PRIORITY 5 AUTO MERGE
insert into "SCHEMA_Q"."DATA_QUALITY_CHECK_RULES"
values(1,
'"SCHEMA_Q"."TABLE1"',
'T1_F7',
'T1_F7 IS NULL OR LENGTH(TRIM(TI_F7)) < 3',
'T1F7_ID SHOULD NOT BE NULL AND THE MINIMUM LENGTH SHOULD BE 3','T1_F7 INVALID',
1);
Dynamic SQL allows you to construct an SQL statement during the execution time of a procedure. While dynamic provides more flexibility in creating SQL statements, it does have the disadvantage of an additional cost at runtime:
PROCEDURE "SCHEMA_Q"."QPACKAGE.PROCEDURES::DQ_CHECKS" (IN PROC_DATE DATE )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
DEFAULT SCHEMA "SCHEMA_Q"
--READS SQL DATA
AS
BEGIN
/**************************************************************************************
# Procedure Name: DQ_CHECKS
# Created By: ME
# Program
# Date: 28/Jul/2016
# -------------------------------------------------------------------------------
# Parameters
# Input : Date
# Output - #NULL
# --------------------------------------------------------------------------------
This procedure does DQ checks
1- manages the status
2 - captures the field in error
3 - holds all subsequent related input records from going upstream
If your SQLScript procedure needs execution of dynamic SQL statements where the parts of it are derived from untrusted input (e.g. user interface), there is a danger of an SQL injection attack. In the below case there is no user input - values are taken from a table.The data parameter procedure will be passed using Data Services and manual run access is provided only to trusted personnel
The code assumes that we are dealing with 1 output tables - TABLE1 It is not made generic for number of tables.
It is generic for the number of conditions for the table in consideration. FOR more tables the same code can be duplicated in the procedurefor each table.
--UNABLE TO USE DYNAMIC SQL HERE -- TODAYDAT = SELECT * FROM "SCHEMA_Q"."TABLE1" WHERE TO_DATE(ENT_TIME) = :PROC_DATE
You cannot bind the result of a dynamic SQL statement to a SQLScript variable
TO DO: iNCLUDE REPROCESSING LOGIC
-------------------------------------------------------------------
# Version Notes
#
# Date Version - Comments - Modified By
# -------------------------------------------------------------------
# 12/09/2016.
*************************************************************************/
DECLARE IM_FILTER_STRING NVARCHAR(200);
DECLARE VALRULEARR NVARCHAR(1000) ARRAY;
DECLARE ERRORCODEARR INTEGER ARRAY;
DECLARE TABNAMEARR VARCHAR(100) ARRAY;
DECLARE COLNAMEARR VARCHAR(50) ARRAY;
DECLARE TABLECNT INT;
DECLARE ARRIND INT;
DECLARE I INT;
DECLARE MAXIND_T2 INT;
DECLARE MAXIND_T1 INT;
--- Processing for TABLE1 Data Quality Error Begins
TODAYDAT_T1 = SELECT * FROM "SCHEMA_Q"."TABLE1" WHERE TO_DATE(ENT_TIME) = :PROC_DATE AND ERR_STATUS = 0;
VALRULE_T1 = SELECT DQERR_ID,COLUMN_NAME,VALIDATION_RULE FROM "SCHEMA_Q"."DATA_QUALITY_CHECK_RULES" WHERE ACTIVE_KEY =1 AND TABLE_NAME ='"SCHEMA_Q"."TABLE1"' ORDER BY DQERR_ID;
VALRULEARR = ARRAY_AGG(:VALRULE_T1.VALIDATION_RULE);
ERRORCODEARR = ARRAY_AGG(:VALRULE_T1.DQERR_ID);
COLNAMEARR = ARRAY_AGG(:VALRULE_T1.COLUMN_NAME);
SELECT COUNT(DQERR_ID) INTO MAXIND_T1 FROM "SCHEMA_Q"."DATA_QUALITY_CHECK_RULES" WHERE ACTIVE_KEY = 1 AND TABLE_NAME = '"SCHEMA_Q"."TABLE1"';
FOR ARRIND IN 1 .. :MAXIND_T1 DO
OUTPUT_TABLE_T1 = APPLY_FILTER(:TODAYDAT_T1,:VALRULEARR[:ARRIND]);
INSERT INTO "SCHEMA_Q"."TABLE1_DQERROR"
SELECT DISTINCT
999,"T1F1_KEY" AS CUSTOMER_ID,
"T1F2_KEY","T1F3_KEY",
"T1F4_KEY",:COLNAMEARR[:ARRIND]
FROM :OUTPUT_TABLE_T1;
UPDATE "SCHEMA_Q"."TABLE1_DQERROR" SET DQERR_ID =:ERRORCODEARR[:ARRIND] WHERE DQERR_ID = 999;
commit;
END FOR;
UPDATE
"SCHEMA_Q"."TABLE1" C
SET
ERR_STATUS = 6 -- dqerror
FROM
"SCHEMA_Q"."TABLE1" C
INNER JOIN
"SCHEMA_Q"."TABLE1_DQERROR" B
ON
B.T1F1_KEY = C.T1F1_KEY
AND TO_DATE(C.ENT_TIME) = :PROC_DATE
AND C.ERR_STATUS = 0;
--- Processing for TABLE 1 Data Quality Error Ends
---FINAL SYNC FOR CURRENT DATA BASED ON PAST DATA
ALL_DQ = SELECT DISTINCT CUSTOMER_ID FROM "SCHEMA_Q"."TABLE1" WHERE ERR_STATUS IN (6,7);
---code for updating dependent hold
END;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
10 | |
10 | |
9 | |
8 | |
7 | |
7 | |
7 | |
7 | |
6 |