CREATE COLUMN TABLE "FI_INPUT" ("NUM" INTEGER CS_INT PRIMARY KEY,
"SCHEMA" NVARCHAR(40),
"TABLE" NVARCHAR(40),
"FIELD" NVARCHAR(100)) UNLOAD PRIORITY 5 AUTO MERGE
;
ALTER TABLE "FI_INPUT" ADD ("FIELD_QUERY" NVARCHAR(200) GENERATED ALWAYS AS '%source="' || UPPER("FIELD") || '"%')
;
ALTER TABLE "FI_INPUT" ADD ("TABLE_QUERY" NVARCHAR(200) GENERATED ALWAYS AS '%<input node="#' || UPPER("TABLE") || '%')
;
CREATE COLUMN TABLE "FI_OUTPUT" ( "TABLE" NVARCHAR(40) CS_STRING,
"FIELD" NVARCHAR(100) CS_STRING,
"PACKAGE" NVARCHAR(300) CS_STRING,
"OBJECT_NAME" NVARCHAR(300) CS_STRING )
;
CREATE COLUMN TABLE "FI_TEMP" ( "ID" INT CS_INT,
"PACKAGE" NVARCHAR(300) CS_STRING,
"OBJ_NAME" NVARCHAR(300) CS_STRING,
"CDATA" NCLOB MEMORY THRESHOLD 1000 )
;
CREATE PROCEDURE FIELD_DEPENDENCY (IN PACKAGE_NAME NVARCHAR(100)) LANGUAGE SQLSCRIPT AS
BEGIN DECLARE I INTEGER
;
DECLARE GEN_QUERY NVARCHAR(1000)
;
DECLARE COUNTER INTEGER
;
DECLARE C1 INTEGER
;
DECLARE PK NVARCHAR(100)
;
PK := ''''||PACKAGE_NAME||'%'''
;
---Find out Existence of table/field in HANA Schemas
T1 = SELECT
TABLE,
FIELD,
CASE WHEN COALESCE(POSITION,
0) = 0
THEN 'WRONG FIELD/TABLE'
ELSE 'YES'
END AS "FIELD_VALIDITY"
FROM FI_INPUT
LEFT OUTER JOIN TABLE_COLUMNS ON TABLE = TABLE_NAME
AND FIELD = COLUMN_NAME
AND SCHEMA = SCHEMA_NAME
;
SELECT COUNT(*) INTO C1 FROM :T1
WHERE FIELD_VALIDITY = 'WRONG FIELD/TABLE';
IF C1 > 0
THEN SELECT
*
FROM :T1
WHERE FIELD_VALIDITY = 'WRONG FIELD/TABLE'
;
ELSE DELETE
FROM FI_OUTPUT
;
DELETE
FROM FI_TEMP
;
SELECT
COUNT(*)
INTO COUNTER
FROM FI_INPUT
;
FOR I IN 1..:COUNTER DO GEN_QUERY = 'INSERT INTO FI_TEMP (SELECT ' ||:I|| ',PACKAGE_ID,OBJECT_NAME ,"CDATA" FROM "_SYS_REPO"."ACTIVE_OBJECT"
WHERE ("PACKAGE_ID" LIKE '||:PK||')
AND (CDATA LIKE (SELECT FIELD_QUERY FROM FI_INPUT WHERE NUM = '||:I||') AND CDATA LIKE (SELECT TABLE_QUERY FROM FI_INPUT WHERE NUM = '||:I||') ))'
;
EXEC (:GEN_QUERY)
;
END FOR
;
INSERT
INTO FI_OUTPUT (SELECT
TABLE,
FIELD,
PACKAGE,
OBJ_NAME
FROM FI_INPUT
INNER JOIN FI_TEMP ON ID = NUM)
;
SELECT
*
FROM FI_OUTPUT
;
END
IF
;
END
;
INSERT INTO "FI_INPUT" VALUES(1,'SCHEMA_NAME','TABLE_NAME','FIELD_NAME');
CALL "FIELD_DEPENDENCY"('PackageName');
INSERT INTO FI_INPUT VALUES(1,'ECC','VBRP','NETWR');
INSERT INTO FI_INPUT VALUES(2,'ECC','VBRK','VBELN');
INSERT INTO FI_INPUT VALUES(3,'ECC','VBRK','ABCD');--THIS IS FOR A TEST
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 | |
3 | |
3 | |
3 |