Troubleshooting¶
Alation Cloud Service Applies to Alation Cloud Service instances of Alation
Customer Managed Applies to customer-managed instances of Alation
Refer to Troubleshoot OCF Connectors.
Type of Error |
Cause |
Solution |
|---|---|---|
The connector responded with the following error message: Username cannot be null, Action: Contact Alation support for further assistance. |
The cause of this error is if the configuration fields are empty. |
Make sure that the configuration fields are populated with the necessary details. |
Cross Lineage not getting populated after Lineage Extraction. |
If Cross Lineage is not constructed after successful MDE. |
Execute the Lineage API, see Cross-System Lineage API for details. Also, verify the data from backend using the queries provided in Oracle Database Queries and SQL Server Database Queries. |
Oracle Database Queries¶
Filter Folder List Query¶
SELECT CAST(SUBJECT_AREA AS VARCHAR(100)) AS CATALOG FROM REP_SUBJECT
Schema Query¶
SELECT DISTINCT
c.subject_area CATALOG,
a.task_name workflow_name,
b.instance_name session_name,
c.mapping_name,
CONCAT(CONCAT(a.task_name,'/'),c.mapping_name) AS SCHEMA
FROM REP_ALL_TASKS a,REP_TASK_INST b,REP_LOAD_SESSIONS c
WHERE a.subject_id=c.subject_id AND a.task_type=71
AND a.task_id=b.workflow_id AND b.task_type=68 AND b.task_id=c.session_id
AND c.MAPPING_ID <> 0
ORDER BY 1,2,3,4
Table Query¶
DECLARE
v_count NUMBER;
BEGIN
-- Check and truncate TEMP_CNX_ATTR
SELECT COUNT(*) INTO v_count FROM USER_TABLES WHERE TABLE_NAME = 'WFSCHEMA';
IF v_count > 0 THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE WFSCHEMA';
EXECUTE IMMEDIATE 'Drop TABLE WFSCHEMA';
DBMS_OUTPUT.PUT_LINE('Table WFSCHEMA truncated.');
ELSE
DBMS_OUTPUT.PUT_LINE('Table WFSCHEMA does not exist.');
END IF;
-- Check and truncate TEMP_MMD_CNX_attr
SELECT COUNT(*) INTO v_count FROM USER_TABLES WHERE TABLE_NAME = 'MCP';
IF v_count > 0 THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE MCP';
EXECUTE IMMEDIATE 'Drop TABLE MCP';
DBMS_OUTPUT.PUT_LINE('TABLE MCP TRUNCATED.');
ELSE
DBMS_OUTPUT.PUT_LINE('Table MCP does not exist.');
END IF;
-- Check and truncate TEMP_MMD_CNX_attr
SELECT COUNT(*) INTO v_count FROM USER_TABLES WHERE TABLE_NAME = 'QUERY1_TABLES';
IF v_count > 0 THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE QUERY1_TABLES';
EXECUTE IMMEDIATE 'Drop TABLE QUERY1_TABLES';
DBMS_OUTPUT.PUT_LINE('TABLE QUERY1_TABLES TRUNCATED.');
ELSE
DBMS_OUTPUT.PUT_LINE('TABLE QUERY1_TABLES DOES NOT EXIST.');
END IF;
-- Check and truncate TEMP_MMD_CNX_attr
SELECT COUNT(*) INTO v_count FROM USER_TABLES WHERE TABLE_NAME = 'CTE';
IF v_count > 0 THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE CTE';
EXECUTE IMMEDIATE 'Drop TABLE CTE';
DBMS_OUTPUT.PUT_LINE('TABLE CTE TRUNCATED.');
ELSE
DBMS_OUTPUT.PUT_LINE('TABLE CTE DOES NOT EXIST.');
END IF;
SELECT COUNT(*) INTO v_count FROM USER_TABLES WHERE TABLE_NAME = 'TEMP_CONPORTS';
IF v_count > 0 THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_CONPORTS';
EXECUTE IMMEDIATE 'Drop TABLE TEMP_CONPORTS';
DBMS_OUTPUT.PUT_LINE('Table TEMP_CONPORTS truncated.');
ELSE
DBMS_OUTPUT.PUT_LINE('Table TEMP_CONPORTS does not exist.');
END IF;
END;
/
-- STEP 1: Create Global Temporary Tables
CREATE GLOBAL TEMPORARY TABLE WFSCHEMA (
CATALOG VARCHAR2(255),
subject_id NUMBER,
mapping_id NUMBER,
SCHEMA VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE MCP (
subject_id NUMBER,
mapping_id NUMBER,
from_object_name VARCHAR2(255),
to_object_name VARCHAR2(255),
from_object_id NUMBER,
to_object_id NUMBER,
from_object_instance_id NUMBER,
from_object_type_name VARCHAR2(255),
from_object_type NUMBER
) ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE QUERY1_TABLES (
SCHEMA VARCHAR2(4000),
CATALOG VARCHAR2(255),
TABLE_NAME VARCHAR2(4000),
EXPRESSION VARCHAR2(4000),
REMARKS VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE CTE (
SUBJECT_AREA VARCHAR2(255),
TASK_NAME VARCHAR2(255),
MAPPING_NAME VARCHAR2(255),
FROM_OBJECT_NAME VARCHAR2(255),
from_object_id NUMBER,
FROM_OBJECT_INSTANCE_ID NUMBER,
FROM_OBJECT_TYPE_NAME VARCHAR2(255),
TO_OBJECT_NAME VARCHAR2(255),
TO_OBJECT_INSTANCE_ID NUMBER,
TO_object_id NUMBER,
TO_OBJECT_TYPE_NAME VARCHAR2(255),
TO_OBJECT_TYPE NUMBER
) ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE TEMP_CONPORTS (
SUBJECT_AREA VARCHAR2(255),
SUBJECT_ID NUMBER
,MAPPING_NAME VARCHAR2(255),
MAPPING_ID NUMBER,
FROM_OBJECT_NAME VARCHAR2(255),
from_object_id NUMBER,
FROM_OBJECT_INSTANCE_ID NUMBER,
FROM_OBJECT_TYPE_NAME VARCHAR2(255),
TO_OBJECT_NAME VARCHAR2(255),
TO_OBJECT_INSTANCE_ID NUMBER,
TO_object_id NUMBER,
TO_OBJECT_TYPE_NAME VARCHAR2(255),
TO_OBJECT_TYPE NUMBER
) ON COMMIT PRESERVE ROWS;
-- STEP 2: Populate Tables
-- WFSCHEMA
INSERT INTO WFSCHEMA
SELECT DISTINCT
c.subject_area,
a.subject_id,
c.mapping_id,
a.task_name || '/' || c.mapping_name
FROM rep_all_tasks a
JOIN rep_task_inst b ON a.task_id = b.workflow_id
JOIN rep_load_sessions c ON a.subject_id = c.subject_id
WHERE a.task_type = 71
AND b.task_type = 68
AND b.task_id = c.session_id
AND c.mapping_id <> 0;
-- mcp
INSERT INTO MCP
SELECT DISTINCT
subject_id,
mapping_id,
from_object_name,
to_object_name,
from_object_id,
to_object_id,
from_object_instance_id,
from_object_type_name,
from_object_type
FROM rep_mapping_conn_ports;
INSERT INTO TEMP_CONPORTS
SELECT SUBJECT_AREA
,SUBJECT_ID
,MAPPING_NAME
,MAPPING_ID
,FROM_OBJECT_NAME
,from_object_id
,FROM_OBJECT_INSTANCE_ID
,FROM_OBJECT_TYPE_NAME
,TO_OBJECT_NAME
,TO_OBJECT_INSTANCE_ID
,TO_object_id
,TO_OBJECT_TYPE_NAME
,TO_OBJECT_TYPE
FROM REP_MAPPING_CONN_PORTS;
-- cte
INSERT INTO CTE
SELECT DISTINCT
e.SUBJECT_AREA,
a.TASK_NAME,
e.MAPPING_NAME,
e.FROM_OBJECT_NAME,
e.from_object_id,
e.FROM_OBJECT_INSTANCE_ID,
e.FROM_OBJECT_TYPE_NAME,
e.TO_OBJECT_NAME,
e.TO_OBJECT_INSTANCE_ID,
e.TO_object_id,
e.TO_OBJECT_TYPE_NAME,
e.TO_OBJECT_TYPE
FROM REP_ALL_TASKS a
JOIN REP_TASK_INST b ON a.TASK_ID = b.WORKFLOW_ID
JOIN REP_LOAD_SESSIONS c ON a.SUBJECT_ID = c.SUBJECT_ID AND b.TASK_ID = c.SESSION_ID
JOIN REP_SESS_WIDGET_CNXS d ON d.SESSION_ID = c.SESSION_ID
----JOIN REP_MAPPING_CONN_PORTS e ON c.MAPPING_ID = e.MAPPING_ID AND c.SUBJECT_ID = e.SUBJECT_ID
JOIN TEMP_CONPORTS e ON c.MAPPING_ID = e.MAPPING_ID AND c.SUBJECT_ID = e.SUBJECT_ID
WHERE b.TASK_TYPE = 68 AND e.MAPPING_ID <> 0;
-- query1_tables: Part 1
INSERT INTO QUERY1_TABLES
SELECT DISTINCT
wf.SCHEMA,
wf.CATALOG,
mc.from_object_id || '/' || mc.from_object_name AS TABLE_NAME,
wfld.expression AS EXPRESSION,
w.widget_type_name AS REMARKS
FROM wfSchema wf
JOIN mcp mc ON mc.subject_id = wf.subject_id AND mc.mapping_id = wf.mapping_id
JOIN rep_widget_inst w ON mc.mapping_id = w.mapping_id
AND mc.from_object_instance_id = w.instance_id
AND w.widget_type_name = mc.from_object_type_name
JOIN rep_widget_field wfld ON w.widget_id = wfld.widget_id
WHERE w.widget_type NOT IN (1, 2, 3)
AND wfld.field_name != wfld.expression;
-- query1_tables: Part 2
INSERT INTO query1_tables
SELECT DISTINCT
wf.SCHEMA,
wf.CATALOG,
attr.from_object_id || '/' || attr.from_object_name AS TABLE_NAME,
attr.attr_value AS EXPRESSION,
attr.attr_name AS REMARKS
FROM WFSCHEMA wf
JOIN (
SELECT DISTINCT
wi.mapping_id,
rls.mapping_name,
rls.subject_id,
attr.attr_name,
attr.attr_value,
mc.from_object_id,
mc.from_object_name
FROM opb_widget_inst wi
JOIN rep_load_sessions rls ON wi.mapping_id = rls.mapping_id
JOIN mcp mc ON mc.mapping_id = wi.mapping_id
AND mc.from_object_type = wi.widget_type
AND mc.from_object_instance_id = wi.instance_id
JOIN (
SELECT widget_id, widget_type, attr_name, attr_value
FROM rep_widget_attr
WHERE widget_type IN (4, 10) AND attr_id = 1 AND attr_type = 2 AND attr_value NOT IN ('0')
UNION ALL
SELECT widget_id, widget_type, attr_name, attr_value
FROM rep_widget_attr
WHERE widget_type = 12 AND attr_id = 3 AND attr_type = 2 AND attr_value NOT IN ('0')
UNION ALL
SELECT widget_id, widget_type, attr_name, attr_value
FROM rep_widget_attr
WHERE widget_type = 80 AND attr_id = 8 AND attr_type = 5 AND attr_value NOT IN ('0')
) attr ON wi.widget_id = attr.widget_id
WHERE wi.widget_type IN (4, 10, 12, 80)
) attr
ON attr.subject_id = wf.subject_id AND attr.mapping_id = wf.mapping_id;
-- query1_tables: Part 3
INSERT INTO query1_tables
SELECT DISTINCT
wf.SCHEMA,
wf.CATALOG,
grp.from_object_id || '/' || grp.from_object_name AS TABLE_NAME,
grp.expression AS EXPRESSION,
grp.REMARKS AS REMARKS
FROM WFSCHEMA wf
JOIN (
SELECT DISTINCT
mc.subject_id,
mc.mapping_id,
mc.from_object_name,
mc.to_object_name,
mc.from_object_id,
mc.to_object_id,
grp1.attr_values AS expression,
'GrpName_FilterCondition' AS REMARKS
FROM mcp mc
JOIN rep_widget_inst wi ON wi.mapping_id = mc.mapping_id
JOIN (
SELECT object_type, object_id,
LISTAGG(attr_value, ', ') WITHIN GROUP (ORDER BY attr_id) AS attr_values
FROM opb_table_group
WHERE group_id IN (2, 5)
AND attr_id <> 5
GROUP BY object_type, object_id, group_id
) grp1 ON wi.widget_id = grp1.object_id
WHERE mc.from_object_type_name = 'Router'
AND wi.widget_type = 15
) grp ON grp.subject_id = wf.subject_id AND grp.mapping_id = wf.mapping_id;
-- STEP 3: Final Output
SELECT CATALOG, SCHEMA, TABLE_NAME, TABLE_TYPE, REMARKS
FROM (
SELECT DISTINCT
e.SUBJECT_AREA AS CATALOG,
e.TASK_NAME || '/' || e.MAPPING_NAME AS SCHEMA,
e.from_object_id || '/' || e.FROM_OBJECT_NAME AS TABLE_NAME,
e.FROM_OBJECT_INSTANCE_ID AS TABLE_TYPE,
e.FROM_OBJECT_TYPE_NAME AS REMARKS
FROM cte e
WHERE e.from_object_id || '/' || e.FROM_OBJECT_NAME NOT IN (
SELECT TABLE_NAME FROM query1_tables
)
)
UNION ALL
SELECT CATALOG, SCHEMA, TABLE_NAME, TABLE_TYPE, REMARKS
FROM (
SELECT DISTINCT
e1.SUBJECT_AREA AS CATALOG,
e1.TASK_NAME || '/' || e1.MAPPING_NAME AS SCHEMA,
e1.to_object_id || '/' || e1.TO_OBJECT_NAME AS TABLE_NAME,
e1.TO_OBJECT_INSTANCE_ID AS TABLE_TYPE,
e1.TO_OBJECT_TYPE_NAME AS REMARKS
FROM cte e1
WHERE e1.TO_OBJECT_TYPE = 2
AND e1.to_object_id || '/' || e1.TO_OBJECT_NAME NOT IN (
SELECT TABLE_NAME FROM query1_tables
)
)
ORDER BY SCHEMA, TABLE_TYPE;
Column Query¶
WITH cte AS (
SELECT DISTINCT
e.subject_area,
a.task_name,
e.mapping_name,
e.from_object_name,
e.from_object_field_name,
e.from_object_type_name,
e.from_object_type,
e.from_object_id,
e.to_object_id,
e.from_object_instance_id,
e.to_object_instance_id,
e.mapping_id,
e.TO_OBJECT_FIELD_NAME,
e.TO_OBJECT_NAME,
e.TO_OBJECT_TYPE,
e.TO_object_type_name
FROM REP_MAPPING_CONN_PORTS e
JOIN REP_LOAD_SESSIONS c ON e.mapping_id = c.mapping_id
AND e.subject_id = c.subject_id
AND e.mapping_id <> 0
JOIN REP_TASK_INST b ON c.session_id = b.task_id
AND b.task_type = 68
JOIN REP_ALL_TASKS a ON b.workflow_id = a.task_id
AND a.task_type = 71
INNER JOIN REP_SESS_WIDGET_CNXS d ON d.session_id = c.session_id
),
cte1 AS (
SELECT DISTINCT
e.subject_area,
a.task_name,
e.mapping_name,
e.from_object_name,
e.from_object_field_name,
e.from_object_type_name,
e.from_object_type,
e.from_object_id,
e.to_object_id,
e.from_object_instance_id,
e.to_object_instance_id,
e.mapping_id,
e.TO_OBJECT_FIELD_NAME,
e.TO_OBJECT_NAME,
e.TO_OBJECT_TYPE,
e.TO_object_type_name
FROM REP_MAPPING_CONN_PORTS e
JOIN REP_LOAD_SESSIONS c ON e.mapping_id = c.mapping_id
AND e.subject_id = c.subject_id
AND e.mapping_id <> 0
JOIN REP_TASK_INST b ON c.session_id = b.task_id
AND b.task_type = 68
JOIN REP_ALL_TASKS a ON b.workflow_id = a.task_id
AND a.task_type = 71
INNER JOIN REP_SESS_WIDGET_CNXS d ON d.session_id = c.session_id
),
cte2 AS (
SELECT DISTINCT
e.subject_area,
a.task_name,
e.mapping_name,
e.from_object_name,
e.from_object_field_name,
e.from_object_type_name,
e.from_object_type,
e.from_object_id,
e.to_object_id,
e.from_object_instance_id,
e.to_object_instance_id,
e.mapping_id,
e.TO_OBJECT_FIELD_NAME,
e.TO_OBJECT_NAME,
e.TO_OBJECT_TYPE,
e.TO_object_type_name
FROM REP_MAPPING_CONN_PORTS e
JOIN REP_LOAD_SESSIONS c ON e.mapping_id = c.mapping_id
AND e.subject_id = c.subject_id
AND e.mapping_id <> 0
JOIN REP_TASK_INST b ON c.session_id = b.task_id
AND b.task_type = 68
JOIN REP_ALL_TASKS a ON b.workflow_id = a.task_id
AND a.task_type = 71
INNER JOIN REP_SESS_WIDGET_CNXS d ON d.session_id = c.session_id
)
SELECT DISTINCT
CTE.subject_area AS catalog,
concat(concat(CTE.task_name, '/'), CTE.mapping_name) AS schema,
CTE.from_object_id || '/' || CTE.from_object_name AS TABLE_NAME,
CTE.from_object_field_name AS column_name,
CTE.from_object_type_name AS remarks,
CTE.from_object_type,
' ' AS COLUMN_DEFAULT,
coalesce(f.column_number, 0) AS ordinal_position,
CASE
WHEN f.source_type IN ( 'varchar2', 'nvarchar', 'nvarchar2', 'number' ) THEN f.source_type || '(' || f.data_precision || ')'
WHEN f.source_type = 'number(p,s)' THEN 'number(' || f.data_precision || ',' || f.data_scale || ')'
WHEN f.source_type IS NOT NULL THEN f.source_type ELSE 'VARCHAR2' END AS type_name,
coalesce(f.source_type, 'VARCHAR2') AS data_type,
0 AS is_nullable
FROM CTE
LEFT JOIN rep_src_tbl_flds f ON CTE.from_object_id = f.table_id
AND CTE.from_object_field_name = f.column_name
WHERE CTE.from_object_type = 1
UNION
SELECT DISTINCT
CTE.subject_area AS catalog,
concat(concat(CTE.task_name, '/'), CTE.mapping_name) AS schema,
CTE.from_object_id || '/' || CTE.from_object_name AS table_name,
CTE.from_object_field_name AS column_name,
CTE.from_object_type_name AS remarks,
CTE.from_object_type,
' ' AS COLUMN_DEFAULT,
coalesce(f.column_number, 0) AS ordinal_position,
CASE
WHEN f.source_type IN ( 'varchar2', 'nvarchar', 'nvarchar2', 'number' ) THEN f.source_type || '(' || f.data_precision || ')'
WHEN f.source_type = 'number(p,s)' THEN 'number(' || f.data_precision || ',' || f.data_scale || ')'
WHEN f.source_type IS NOT NULL THEN f.source_type ELSE 'VARCHAR2' END AS type_name,
coalesce(f.source_type, 'VARCHAR2') AS data_type,
0 AS is_nullable
FROM CTE
LEFT JOIN rep_src_tbl_flds f ON CTE.from_object_id = f.table_id
AND CTE.from_object_field_name = f.column_name
WHERE CTE.from_object_type = 1
UNION
SELECT DISTINCT
CTE1.subject_area AS catalog,
concat(concat(CTE1.task_name, '/'), CTE1.mapping_name) AS schema,
CTE1.from_object_id || '/' || CTE1.from_object_name AS table_name,
CTE1.from_object_field_name AS column_name,
CTE1.from_object_type_name AS remarks,
CTE1.from_object_type,
' ' AS COLUMN_DEFAULT,
coalesce(h.field_id, 0) AS ordinal_position,
CASE WHEN h.datatype IN ( 'varchar2', 'nvarchar', 'nvarchar2', 'number' ) THEN h.datatype || '(' || h.wgt_datatype || ')'
WHEN h.datatype = 'number(p,s)' THEN 'number(' || h.wgt_prec || ',' || h.wgt_scale || ')'
WHEN h.datatype IS NOT NULL THEN h.datatype ELSE 'VARCHAR2' END AS type_name,
coalesce(h.datatype, 'VARCHAR2') AS data_type,
0 AS is_nullable
FROM CTE1
LEFT JOIN rep_widget_field h ON CTE1.FROM_object_id = h.widget_id
AND CTE1.from_object_field_name = h.field_name
WHERE CTE1.from_object_type NOT IN (1,2)
UNION
SELECT DISTINCT
cte1.SUBJECT_AREA AS CATALOG,
CONCAT(CONCAT(cte1.task_name, '/'), cte1.mapping_name) AS SCHEMA,
cte1.TO_object_id || '/' || cte1.TO_OBJECT_NAME AS TABLE_NAME,
cte1.TO_OBJECT_FIELD_NAME AS COLUMN_NAME,
cte1.TO_object_type_name AS REMARKS,
cte1.TO_object_type,
' ' AS COLUMN_DEFAULT,
COALESCE(h.field_id, 0) AS ORDINAL_POSITION,
CASE
WHEN H.DATATYPE IN ('varchar2', 'nvarchar', 'nvarchar2', 'number') THEN H.DATATYPE || '(' || H.WGT_DATATYPE || ')'
WHEN H.DATATYPE = 'number(p,s)' THEN 'number(' || H.WGT_PREC || ',' || H.WGT_SCALE || ')'
WHEN H.DATATYPE IS NOT NULL THEN H.DATATYPE
ELSE 'VARCHAR2' END AS TYPE_NAME,
COALESCE(h.DATATYPE, 'VARCHAR2') AS DATA_TYPE,
0 AS IS_NULLABLE
FROM CTE1
LEFT JOIN rep_widget_field H ON cte1.TO_OBJECT_ID = H.WIDGET_ID
AND cte1.TO_OBJECT_FIELD_NAME = h.FIELD_NAME
WHERE cte1.to_object_type NOT IN (1,2)
UNION
SELECT DISTINCT
cte2.SUBJECT_AREA AS CATALOG,
CONCAT(CONCAT(cte2.task_name, '/'), cte2.mapping_name) AS SCHEMA,
cte2.TO_object_id || '/' || cte2.TO_OBJECT_NAME AS TABLE_NAME,
cte2.TO_OBJECT_FIELD_NAME AS COLUMN_NAME,
cte2.TO_object_type_name AS REMARKS,
cte2.TO_object_type,
' ' AS COLUMN_DEFAULT,
COALESCE(i.TARGET_FIELD_NUMBER, 0) AS ORDINAL_POSITION,
CASE
WHEN i.TARGET_FIELD_DATATYPE IN ('varchar2', 'nvarchar', 'nvarchar2', 'number') THEN i.TARGET_FIELD_DATATYPE || '(' || i.TARGET_FIELD_PRECISION || ')'
WHEN i.TARGET_FIELD_DATATYPE = 'number(p,s)' THEN 'number(' || i.TARGET_FIELD_PRECISION || ',' || i.TARGET_FIELD_SCALE || ')'
WHEN i.TARGET_FIELD_DATATYPE IS NOT NULL THEN i.TARGET_FIELD_DATATYPE
ELSE 'VARCHAR2' END AS TYPE_NAME,
COALESCE(i.TARGET_FIELD_DATATYPE, 'VARCHAR2') AS DATA_TYPE,
0 AS IS_NULLABLE
FROM CTE2
LEFT JOIN rep_all_target_flds i ON ( CTE2.to_object_id = i.TARGET_ID
AND CTE2.to_object_field_name = i.TARGET_FIELD_NAME )
WHERE CTE2.to_object_type = 2
Foreign Key Query¶
WITH cte AS (
SELECT
SUBJECT_AREA
,subject_id
,mapping_name
,mapping_id
,from_object_id
,FROM_OBJECT_NAME
,FROM_OBJECT_FIELD_NAME
,to_object_id
,TO_OBJECT_NAME
,TO_OBJECT_FIELD_NAME
FROM REP_MAPPING_CONN_PORTS
)
SELECT
DISTINCT
e.SUBJECT_AREA AS PK_CATALOG,
a.task_name || '/' || e.mapping_name AS PK_SCHEMA,
e.from_object_id || '/' || e.FROM_OBJECT_NAME AS PK_TABLE,
e.FROM_OBJECT_FIELD_NAME AS PK_COLUMN,
e.SUBJECT_AREA AS FK_CATALOG,
a.task_name || '/' || e.mapping_name AS FK_SCHEMA,
e.to_object_id || '/' || e.TO_OBJECT_NAME AS FK_TABLE,
e.TO_OBJECT_FIELD_NAME AS FK_COLUMN
FROM cte e
JOIN REP_LOAD_SESSIONS c
ON e.mapping_id = c.mapping_id
AND e.subject_id = c.subject_id
AND e.mapping_id <> 0
JOIN REP_TASK_INST b
ON c.session_id = b.task_id
AND b.task_type = 68
JOIN REP_ALL_TASKS a
ON b.workflow_id = a.task_id
AND A.task_type=71
INNER JOIN REP_SESS_WIDGET_CNXS d
ON d.session_id = c.session_id
ORDER BY 1, 2, 3, 4
Transformation Expression and Condition Query¶
SELECT wfSchema.CATALOG, wfSchema.subject_id,wfSchema.SCHEMA,
exp.from_object_name AS TABLE_NAME,exp.Remarks,exp.expression,exp.Column_Name
FROM (SELECT DISTINCT c.subject_area AS CATALOG,
a.subject_id,
c.mapping_id,
Concat(Concat(a.task_name, '/'), c.mapping_name) AS SCHEMA
FROM rep_all_tasks a,
rep_task_inst b,
rep_load_sessions c
WHERE a.subject_id = c.subject_id
AND a.task_type = 71
AND a.task_id = b.workflow_id
AND b.task_type = 68
AND b.task_id = c.session_id
AND c.mapping_id <> 0
) wfSchema
INNER JOIN (SELECT DISTINCT mcp.subject_id,
mcp.mapping_id,
mcp.from_object_name,
wf.expression AS EXPRESSION,
wi.widget_type_name AS Remarks,
wf.field_name AS Column_Name
FROM rep_mapping_conn_ports mcp,
rep_widget_inst wi,
rep_widget_field wf
WHERE (mcp.mapping_id = wi.mapping_id
AND mcp.from_object_instance_id = wi.instance_id
AND wi.widget_type_name = mcp.from_object_type_name)
AND (wi.widget_type NOT IN ( 1, 2, 3 ))
AND (wi.widget_id = wf.widget_id )
AND (wf.field_name != wf.expression)
)exp
ON exp.subject_id = wfSchema.subject_id
AND exp.mapping_id = wfSchema.mapping_id
Verify Data (Transformation Expression)¶
This query can be used for conditions like Filter Condition, Joiner Condition, and more.
SELECT
wfSchema.CATALOG, wfSchema.subject_id, wfSchema.SCHEMA,
attr1.instance_name AS TABLE_NAME, attr1.widget_type, attr1.widget_id, attr1.attr_name AS REMARKS,
attr1.attr_value AS Condition
FROM (SELECT DISTINCT c.subject_area AS CATALOG,
a.subject_id,
c.mapping_id,
Concat(Concat(a.task_name, '/'), c.mapping_name) AS SCHEMA
FROM rep_all_tasks a,
rep_task_inst b,
rep_load_sessions c
WHERE a.subject_id = c.subject_id
AND a.task_type = 71
AND a.task_id = b.workflow_id
AND b.task_type = 68
AND b.task_id = c.session_id
AND c.mapping_id <> 0
) wfSchema
INNER JOIN (
SELECT wi.mapping_id,
rls.mapping_name,
wi.widget_id,
instance_id,
wi.instance_name,
rls.subject_id,
rls.session_id,
attr.attr_name,
attr.attr_value,
attr.widget_type
FROM
opb_widget_inst wi,
rep_load_sessions rls,
(
SELECT wa.widget_id ,wa.widget_type,wa.attr_name,wa.attr_value FROM rep_widget_attr wa WHERE wa.widget_type=10 AND wa.attr_id=1 AND wa.attr_type=2 AND wa.attr_value NOT IN ('0')
UNION ALL
SELECT wa.widget_id,wa.widget_type,wa.attr_name,wa.attr_value FROM rep_widget_attr wa WHERE wa.widget_type=12 AND wa.attr_id=3 AND wa.attr_type=2 AND wa.attr_value NOT IN ('0')
UNION ALL
SELECT wa.widget_id,wa.widget_type,wa.attr_name,wa.attr_value FROM rep_widget_attr wa WHERE wa.widget_type=80 AND wa.attr_id=8 AND wa.attr_type=5 AND wa.attr_value NOT IN ('0')
)attr
WHERE wi.widget_type IN (10,12,80)
AND wi.mapping_id=rls.mapping_id
AND wi.widget_id=attr.widget_id
AND rls.subject_id!=9
) attr1
ON attr1.subject_id = wfSchema.subject_id
AND attr1.mapping_id = wfSchema.mapping_id
Connection Details Query¶
SELECT
a.Object_ID,a.Object_Name,a.Object_Subtype,
a.user_name,a.connect_string,
MAX(CASE WHEN c.ATTR_NAME = 'Database name' THEN b.attr_value END) AS DATABASE_Name,
MAX(CASE WHEN c.ATTR_NAME = 'Server name' THEN b.attr_value END) AS ServerName
FROM opb_cnx a
INNER JOIN
opb_cnx_ATTR b ON a.Object_ID = b.Object_ID
INNER JOIN
OPB_MMD_CNX_attr c ON b.ATTR_ID = c.ATTR_ID
AND b.Object_Subtype = c.Object_Subtype
GROUP BY
a.Object_ID,a.Object_Name,a.Object_Subtype,a.user_name,a.connect_string
Schema Name for Source and Target¶
SELECT
CATALOG,SOURCE_SCHEMA,TABLE_NAME,TARGET_SCHEMA,CONNECTION_NAME FROM(SELECT
rp.mapping_id,
rp.session_id,
CONCAT(si.workflow_name,CONCAT('/',rp.mapping_name)) AS TARGET_SCHEMA,
rp.subject_area AS CATALOG,
rp.subject_id,
wi.widget_type,
wi.widget_type_name,
wi.instance_id,
wi.instance_name AS TABLE_NAME,
ati.attr_value AS SOURCE_SCHEMA,
si.connection_name
FROM rep_load_sessions rp,
rep_widget_inst wi,
(SELECT case when is_target=0 then 1 else 2 end AS widget_type,
si.subject_area,
si.subject_id,
si.connection_name,
si.workflow_name,
si.session_id
FROM rep_session_instances si
) si,
(SELECT
sat.session_id,
sat.attr_value,
sat.attr_id ,
case when sat.attr_id=3 then 2
when sat.attr_id=28 then 1
end AS widget_type,
sat.sess_widg_inst_id,
inst.instance_id
FROM opb_swidget_attr sat,
opb_swidget_inst inst
WHERE sat.session_id=inst.session_id
AND sat.sess_widg_inst_id=inst.sess_widg_inst_id
AND sat.attr_id in(3,28)) ati
WHERE (rp.mapping_id=wi.mapping_id AND rp.subject_id=wi.subject_id)
AND (rp.session_id=si.session_id AND rp.subject_id=si.subject_id)
AND (rp.session_id=ati.session_id AND wi.instance_id=ati.instance_id)
AND wi.widget_type=si.widget_type
AND wi.widget_type in (1,2))
SQL Server Database Queries¶
Filter Folder List Query¶
SELECT SUBJECT_AREA AS CATALOG FROM REP_SUBJECT
Schema Query¶
SELECT DISTINCT
c.subject_area CATALOG,
a.task_name workflow_name,
b.instance_name session_name,
c.mapping_name,
CONCAT(CONCAT(a.task_name,'/'),c.mapping_name) AS 'SCHEMA'
FROM REP_ALL_TASKS a,REP_TASK_INST b,REP_LOAD_SESSIONS c
WHERE a.subject_id=c.subject_id AND a.task_type=71
AND a.task_id=b.workflow_id AND b.task_type=68 AND b.task_id=c.session_id
AND c.MAPPING_ID <> 0
ORDER BY 1,2,3,4
Table Query¶
DROP TABLE IF EXISTS #temp
DROP TABLE IF EXISTS #temp_1
DROP TABLE IF EXISTS #temp_2
DROP TABLE if exists #query1_tables
DROP TABLE if exists #wfSchema
DROP TABLE if exists #mcp
SELECT DISTINCT
c.subject_area AS [CATALOG],
a.subject_id,
c.mapping_id,
concat( a.task_name , '/' ,c.mapping_name )AS [SCHEMA]
INTO #wfSchema
FROM rep_all_tasks a
JOIN rep_task_inst b ON a.task_id = b.workflow_id AND a.task_type = 71
JOIN rep_load_sessions c ON a.subject_id = c.subject_id AND b.task_id = c.session_id
WHERE c.mapping_id <> 0
SELECT DISTINCT
subject_id,
mapping_id,
from_object_name,
to_object_name,
from_object_id,
to_object_id,
from_object_instance_id,
from_object_type_name,
from_object_type
INTO #mcp
FROM rep_mapping_conn_ports
SELECT
[SCHEMA],[CATALOG], TABLE_NAME, EXPRESSION, REMARKS
INTO #query1_tables
FROM (
SELECT DISTINCT [SCHEMA],[CATALOG], FROM_TABLE AS TABLE_NAME,EXPRESSION,REMARKS
FROM (
SELECT
wfSchema.[CATALOG] AS [CATALOG],
wfSchema.[SCHEMA] AS [SCHEMA],
CONCAT(exp.from_object_id , '/' , exp.from_object_name) AS FROM_TABLE,
CONCAT(exp.to_object_id , '/' , exp.to_object_name ) AS TO_TABLE,
cast(exp.expression AS NVARCHAR(MAX)) AS EXPRESSION,
exp.Remarks AS REMARKS
FROM #wfSchema AS wfSchema
INNER JOIN (
SELECT DISTINCT
mcp.subject_id,
mcp.mapping_id,
mcp.from_object_name,
mcp.to_object_name,
mcp.from_object_id,
mcp.to_object_id,
CAST(wf.expression AS NVARCHAR(MAX))AS EXPRESSION,
wi.widget_type_name AS Remarks
FROM #MCP AS mcp
JOIN REP_WIDGET_INST WI
ON MCP.MAPPING_ID = WI.MAPPING_ID
AND MCP.FROM_OBJECT_INSTANCE_ID = WI.INSTANCE_ID
AND WI.WIDGET_TYPE_NAME = MCP.FROM_OBJECT_TYPE_NAME
JOIN REP_WIDGET_FIELD WF
ON WI.WIDGET_ID = WF.WIDGET_ID
WHERE
WI.WIDGET_TYPE NOT IN (1, 2, 3)
AND WF.FIELD_NAME != CAST(WF.EXPRESSION AS NVARCHAR(MAX))
) EXP
ON EXP.SUBJECT_ID = WFSCHEMA.SUBJECT_ID
AND EXP.MAPPING_ID = WFSCHEMA.MAPPING_ID
)SUQUERY1
UNION ALL
SELECT DISTINCT
[SCHEMA],[CATALOG],FROM_TABLE AS TABLE_NAME, EXPRESSION , REMARKS
FROM (
SELECT
wfSchema.[CATALOG] AS [CATALOG],
wfSchema.[SCHEMA] AS [SCHEMA],
attr1.FROM_TABLE AS FROM_TABLE,
attr1.TO_TABLE AS TO_TABLE,
CAST(attr1.attr_value AS NVARCHAR(MAX))AS EXPRESSION,
attr1.attr_name AS REMARKS
FROM #wfSchema AS wfSchema
INNER JOIN (
SELECT DISTINCT
wi.mapping_id,
rls.mapping_name,
rls.subject_id,
attr.attr_name,
CAST(attr.attr_value AS NVARCHAR(MAX)) AS attr_value,
CONCAT( mcp.from_object_id , '/' , mcp.from_object_name) AS FROM_TABLE,
CONCAT(mcp.to_object_id , '/' , mcp.to_object_name) AS TO_TABLE
FROM opb_widget_inst wi
JOIN rep_load_sessions rls
ON wi.mapping_id = rls.mapping_id
JOIN #mcp mcp
ON mcp.mapping_id = wi.mapping_id
AND wi.widget_type IN (4,10, 12, 80)
AND mcp.from_object_type = wi.widget_type
AND mcp.from_object_instance_id = wi.instance_id
JOIN (
SELECT
wa.widget_id, wa.widget_type, wa.attr_name, wa.attr_value
FROM rep_widget_attr wa
WHERE
wa.widget_type in (4,10)
AND wa.attr_id = 1
AND wa.attr_type = 2
AND cast(wa.attr_value AS NVARCHAR(max)) NOT IN ('0')
UNION ALL
SELECT
wa.widget_id, wa.widget_type, wa.attr_name, wa.attr_value
FROM rep_widget_attr wa
WHERE
wa.widget_type = 12
AND wa.attr_id = 3
AND wa.attr_type = 2
AND cast(wa.attr_value AS NVARCHAR(MAX)) NOT IN ('0')
UNION ALL
SELECT
wa.widget_id, wa.widget_type, wa.attr_name, wa.attr_value
FROM rep_widget_attr wa
WHERE
wa.widget_type = 80
AND wa.attr_id = 8
AND wa.attr_type = 5
AND CAST(wa.attr_value AS NVARCHAR(MAX)) NOT IN ('0')
) attr
ON wi.widget_id = attr.widget_id
) attr1
ON attr1.subject_id = wfSchema.subject_id
AND attr1.mapping_id = wfSchema.mapping_id
)SUQUERY2
UNION ALL
SELECT DISTINCT
[SCHEMA],[CATALOG],FROM_TABLE AS TABLE_NAME,EXPRESSION,REMARKS
FROM (
SELECT
wfSchema.[CATALOG] AS [CATALOG],
wfSchema.[SCHEMA] AS [SCHEMA],
concat(grp.from_object_id , '/' , grp.from_object_name) AS FROM_TABLE,
concat(grp.to_object_id , '/' , grp.to_object_name ) AS TO_TABLE,
grp.EXPRESSION AS EXPRESSION,
grp.REMARKS AS REMARKS
FROM
#wfSchema AS wfSchema
INNER JOIN (
SELECT DISTINCT
mcp.subject_id,
mcp.mapping_id,
mcp.from_object_name,
mcp.to_object_name,
mcp.from_object_id,
mcp.to_object_id,
grp1.attr_values AS EXPRESSION,
'GrpName_FilterCondition' AS REMARKS
FROM #mcp mcp
INNER JOIN rep_widget_inst wi
ON wi.mapping_id = mcp.mapping_id
INNER JOIN (
SELECT
grp.object_type,
grp.object_id,
STRING_AGG(cast(grp.attr_value AS nvarchar(max)), ', ')
WITHIN GROUP (ORDER BY grp.attr_id) AS attr_values
FROM opb_table_group grp
WHERE
grp.group_id IN (2, 5)
AND grp.attr_id <> 5
GROUP BY grp.object_type, grp.object_id
) grp1
ON wi.widget_id = grp1.object_id
WHERE
mcp.from_object_type_name IN ('Router')
AND wi.widget_type = 15
) grp
ON grp.subject_id = wfSchema.subject_id
AND grp.mapping_id = wfSchema.mapping_id
)SUQUERY3
) AS allsubuires
ORDER BY [SCHEMA], [CATALOG] DESC
---tbale query starting
SELECT session_id
INTO #temp
FROM REP_SESS_WIDGET_CNXS;
SELECT DISTINCT
e.SUBJECT_AREA,
a.TASK_NAME,
e.MAPPING_NAME,
e.FROM_OBJECT_NAME,
e.FROM_OBJECT_ID,
e.FROM_OBJECT_INSTANCE_ID,
e.FROM_OBJECT_TYPE_NAME,
e.TO_OBJECT_NAME,
e.TO_OBJECT_ID,
e.TO_OBJECT_INSTANCE_ID,
e.TO_OBJECT_TYPE_NAME,
e.TO_OBJECT_TYPE
INTO #temp_1
FROM REP_MAPPING_CONN_PORTS e
JOIN REP_LOAD_SESSIONS c
ON e.mapping_id = c.mapping_id
AND e.subject_id = c.subject_id
AND e.mapping_id <> 0
JOIN REP_TASK_INST b
ON c.session_id = b.task_id
AND b.task_type = 68
JOIN REP_ALL_TASKS a
ON b.workflow_id = a.task_id
INNER JOIN #temp d
ON d.session_id = c.session_id
SELECT *
INTO #TEMP_MAIN
FROM (
SELECT CATALOG, \"SCHEMA\", TABLE_NAME, TABLE_TYPE, REMARKS
FROM (
SELECT DISTINCT
e.SUBJECT_AREA AS CATALOG,
CONCAT(CONCAT(e.TASK_NAME, '/'), e.MAPPING_NAME) AS \"SCHEMA\",
CONCAT(CONCAT(e.FROM_OBJECT_ID, '/'), e.FROM_OBJECT_NAME) AS TABLE_NAME,
e.FROM_OBJECT_INSTANCE_ID AS TABLE_TYPE,
e.FROM_OBJECT_TYPE_NAME AS REMARKS
FROM #temp_1 e
) subquery1
UNION
SELECT CATALOG, \"SCHEMA\", TABLE_NAME, TABLE_TYPE, REMARKS
FROM (
SELECT DISTINCT
E1.SUBJECT_AREA AS CATALOG,
CONCAT(CONCAT(E1.TASK_NAME, '/'), E1.MAPPING_NAME) AS \"SCHEMA\",
CONCAT(CONCAT(E1.TO_OBJECT_ID, '/'), E1.TO_OBJECT_NAME) AS TABLE_NAME,
E1.TO_OBJECT_INSTANCE_ID AS TABLE_TYPE,
E1.TO_OBJECT_TYPE_NAME AS REMARKS
FROM #temp_1 E1
WHERE E1.TO_OBJECT_TYPE = 2
) subquery2
) MAINSUB
SELECT * FROM #TEMP_MAIN
WHERE TABLE_NAME NOT IN (
SELECT TABLE_NAME FROM #query1_tables)
ORDER BY 1
DROP TABLE IF EXISTS #TEMP_MAIN"
Column Query¶
DROP TABLE IF EXISTS #TEMP_1
DROP TABLE IF EXISTS #temp_2
SELECT
TASK_TYPE, TASK_ID, WORKFLOW_ID
INTO #temp_2
FROM REP_TASK_INST
WHERE TASK_TYPE = 68
SELECT DISTINCT
E.SUBJECT_AREA,
A.TASK_NAME,
E.MAPPING_NAME,
E.FROM_OBJECT_NAME,
E.FROM_OBJECT_FIELD_NAME,
E.FROM_OBJECT_TYPE_NAME,
E.FROM_OBJECT_TYPE,
E.FROM_OBJECT_ID,
E.TO_OBJECT_ID,
E.FROM_OBJECT_INSTANCE_ID,
E.TO_OBJECT_INSTANCE_ID,
E.MAPPING_ID,
E.TO_OBJECT_FIELD_NAME,
E.TO_OBJECT_NAME,
E.TO_OBJECT_TYPE,
E.TO_OBJECT_TYPE_NAME
INTO #TEMP_1
FROM REP_MAPPING_CONN_PORTS E
JOIN REP_LOAD_SESSIONS (NOLOCK) C
ON E.MAPPING_ID = C.MAPPING_ID
AND E.SUBJECT_ID = C.SUBJECT_ID
AND E.MAPPING_ID <> 0
JOIN #temp_2 B
ON C.SESSION_ID = B.TASK_ID
JOIN REP_ALL_TASKS (NOLOCK) A
ON B.WORKFLOW_ID = A.TASK_ID
AND A.TASK_TYPE = 71
INNER JOIN REP_SESS_WIDGET_CNXS (NOLOCK) D
ON D.SESSION_ID = C.SESSION_ID
SELECT DISTINCT
#TEMP_1.SUBJECT_AREA AS CATALOG,
CONCAT(CONCAT(#TEMP_1.TASK_NAME, '/'), #TEMP_1.MAPPING_NAME) AS \"SCHEMA\",
CONCAT(CONCAT(#TEMP_1.FROM_OBJECT_ID, '/'), #TEMP_1.FROM_OBJECT_NAME) AS TABLE_NAME,
#TEMP_1.FROM_OBJECT_FIELD_NAME AS COLUMN_NAME,
#TEMP_1.FROM_OBJECT_TYPE_NAME AS REMARKS,
'' AS COLUMN_DEFAULT,
#TEMP_1.FROM_OBJECT_TYPE,
COALESCE(F.COLUMN_NUMBER, 0) AS ORDINAL_POSITION,
CASE
WHEN f.source_type IN ('varchar', 'nvarchar', 'number')
THEN f.source_type + '(' + CAST(f.data_precision AS VARCHAR) + ')'
WHEN f.source_type = 'number(p,s)'
THEN 'number(' + CAST(f.data_precision AS VARCHAR) + ',' + CAST(f.data_scale AS VARCHAR) + ')'
WHEN f.source_type IS NOT NULL
THEN f.source_type
ELSE 'VARCHAR'
END AS TYPE_NAME,
COALESCE(f.source_type, 'VARCHAR2') AS DATA_TYPE,
'' AS IS_NULLABLE
FROM #TEMP_1
LEFT JOIN REP_SRC_TBL_FLDS (NOLOCK) F
ON #TEMP_1.FROM_OBJECT_ID = F.TABLE_ID
AND #TEMP_1.FROM_OBJECT_FIELD_NAME = F.COLUMN_NAME
WHERE #TEMP_1.FROM_OBJECT_TYPE = 1
UNION
SELECT DISTINCT
#TEMP_1.SUBJECT_AREA AS CATALOG,
CONCAT(CONCAT(#TEMP_1.TASK_NAME, '/'), #TEMP_1.MAPPING_NAME) AS \"SCHEMA\",
CONCAT(CONCAT(#TEMP_1.FROM_OBJECT_ID, '/'), #TEMP_1.FROM_OBJECT_NAME) AS TABLE_NAME,
#TEMP_1.FROM_OBJECT_FIELD_NAME AS COLUMN_NAME,
#TEMP_1.FROM_OBJECT_TYPE_NAME AS REMARKS,
'' AS COLUMN_DEFAULT,
#TEMP_1.FROM_OBJECT_TYPE,
COALESCE(H.FIELD_ID, 0) AS ORDINAL_POSITION,
CASE
WHEN h.datatype IN ('varchar', 'nvarchar', 'number')
THEN h.datatype + '(' + CAST(h.wgt_datatype AS VARCHAR) + ')'
WHEN h.datatype = 'number(p,s)'
THEN 'number(' + CAST(h.wgt_prec AS VARCHAR) + ',' + CAST(h.wgt_scale AS VARCHAR) + ')'
WHEN h.datatype IS NOT NULL
THEN h.datatype
ELSE 'VARCHAR'
END AS TYPE_NAME,
COALESCE(h.datatype, 'VARCHAR2') AS DATA_TYPE,
'' AS IS_NULLABLE
FROM #TEMP_1 (NOLOCK)
INNER JOIN REP_WIDGET_FIELD (NOLOCK) H
ON #TEMP_1.FROM_OBJECT_ID = H.WIDGET_ID
AND #TEMP_1.FROM_OBJECT_FIELD_NAME = H.FIELD_NAME
WHERE #TEMP_1.FROM_OBJECT_TYPE NOT IN (1, 2)
UNION
SELECT DISTINCT
#TEMP_1.SUBJECT_AREA AS CATALOG,
CONCAT(CONCAT(#TEMP_1.TASK_NAME, '/'), #TEMP_1.MAPPING_NAME) AS \"SCHEMA\",
CONCAT(CONCAT(#TEMP_1.TO_OBJECT_ID, '/'), #TEMP_1.TO_OBJECT_NAME) AS TABLE_NAME,
#TEMP_1.TO_OBJECT_FIELD_NAME AS COLUMN_NAME,
#TEMP_1.TO_OBJECT_TYPE_NAME AS REMARKS,
'' AS COLUMN_DEFAULT,
#TEMP_1.TO_OBJECT_TYPE,
COALESCE(H.FIELD_ID, 0) AS ORDINAL_POSITION,
CASE
WHEN H.DATATYPE IN ('varchar', 'nvarchar', 'number')
THEN H.DATATYPE + '(' + CAST(H.WGT_DATATYPE AS VARCHAR) + ')'
WHEN H.DATATYPE = 'number(p,s)'
THEN 'number(' + CAST(H.WGT_PREC AS VARCHAR) + ',' + CAST(H.WGT_SCALE AS VARCHAR) + ')'
WHEN H.DATATYPE IS NOT NULL
THEN H.DATATYPE
ELSE 'VARCHAR'
END AS TYPE_NAME,
COALESCE(H.DATATYPE, 'VARCHAR2') AS DATA_TYPE,
'' AS IS_NULLABLE
FROM #TEMP_1 (NOLOCK)
INNER JOIN REP_WIDGET_FIELD (NOLOCK) H
ON #TEMP_1.TO_OBJECT_ID = H.WIDGET_ID
AND #TEMP_1.TO_OBJECT_FIELD_NAME = H.FIELD_NAME
WHERE #TEMP_1.TO_OBJECT_TYPE NOT IN (1, 2)
UNION
SELECT DISTINCT
#TEMP_1.SUBJECT_AREA AS CATALOG,
CONCAT(CONCAT(#TEMP_1.TASK_NAME, '/'), #TEMP_1.MAPPING_NAME) AS \"SCHEMA\",
CONCAT(CONCAT(#TEMP_1.TO_OBJECT_ID, '/'),#TEMP_1.TO_OBJECT_NAME) AS TABLE_NAME,
#TEMP_1.TO_OBJECT_FIELD_NAME AS COLUMN_NAME,
#TEMP_1.TO_OBJECT_TYPE_NAME AS REMARKS,
'' AS COLUMN_DEFAULT,
#TEMP_1.TO_OBJECT_TYPE,
COALESCE(I.TARGET_FIELD_NUMBER, 0) AS ORDINAL_POSITION,
CASE
WHEN I.TARGET_FIELD_DATATYPE IN ('varchar', 'nvarchar', 'number')
THEN I.TARGET_FIELD_DATATYPE + '(' + CAST(I.TARGET_FIELD_PRECISION AS VARCHAR) + ')'
WHEN I.TARGET_FIELD_DATATYPE = 'number(p,s)'
THEN 'number(' + CAST(I.TARGET_FIELD_PRECISION AS VARCHAR) + ',' + CAST(I.TARGET_FIELD_SCALE AS VARCHAR) + ')'
WHEN I.TARGET_FIELD_DATATYPE IS NOT NULL
THEN I.TARGET_FIELD_DATATYPE
ELSE 'VARCHAR'
END AS TYPE_NAME,
COALESCE(I.TARGET_FIELD_DATATYPE, 'VARCHAR') AS DATA_TYPE,
'' AS IS_NULLABLE
FROM #TEMP_1 (NOLOCK)
LEFT JOIN rep_all_target_flds (NOLOCK) I
ON #TEMP_1.TO_OBJECT_ID = I.TARGET_ID
AND #TEMP_1.TO_OBJECT_FIELD_NAME = I.TARGET_FIELD_NAME
WHERE #TEMP_1.TO_OBJECT_TYPE = 2
Foreign Key Query¶
DROP TABLE IF EXISTS #temp
SELECT
e.SUBJECT_AREA,
e.mapping_name,
e.FROM_OBJECT_NAME,
e.FROM_OBJECT_FIELD_NAME,
e.FROM_OBJECT_ID,
e.TO_OBJECT_NAME,
e.TO_OBJECT_FIELD_NAME,
e.TO_OBJECT_ID,
e.mapping_id,
e.subject_id
INTO #temp
FROM REP_MAPPING_CONN_PORTS e
SELECT DISTINCT
e.SUBJECT_AREA AS PK_CATALOG,
CONCAT(CONCAT(a.task_name, '/'), e.mapping_name) AS PK_SCHEMA,
CONCAT(CONCAT(e.FROM_OBJECT_ID, '/'),e.FROM_OBJECT_NAME) AS PK_TABLE,
e.FROM_OBJECT_FIELD_NAME AS PK_COLUMN,
e.SUBJECT_AREA AS FK_CATALOG,
CONCAT(CONCAT(a.task_name, '/'), e.mapping_name) AS FK_SCHEMA,
CONCAT(CONCAT(e.TO_OBJECT_ID, '/'), e.TO_OBJECT_NAME) AS FK_TABLE,
e.TO_OBJECT_FIELD_NAME AS FK_COLUMN
FROM #temp e
JOIN REP_LOAD_SESSIONS c
ON e.mapping_id = c.mapping_id
AND e.subject_id = c.subject_id
AND e.mapping_id <> 0
JOIN REP_TASK_INST b
ON c.session_id = b.task_id
AND b.task_type = 68
JOIN REP_ALL_TASKS a
ON b.workflow_id = a.task_id
WHERE a.task_type = 71
ORDER BY 1, 2, 3, 4
Transformation Column Expression Query¶
SELECT wfSchema.CATALOG, wfSchema.subject_id,wfSchema."SCHEMA",
exp.from_object_name AS TABLE_NAME,exp.Remarks,
exp.expression,exp.Column_Name
FROM (SELECT DISTINCT c.subject_area AS CATALOG,
a.subject_id,
c.mapping_id,
Concat(Concat(a.task_name, '/'), c.mapping_name) AS "SCHEMA"
FROM rep_all_tasks a,
rep_task_inst b,
rep_load_sessions c
WHERE a.subject_id = c.subject_id
AND a.task_type = 71
AND a.task_id = b.workflow_id
AND b.task_type = 68
AND b.task_id = c.session_id
AND c.mapping_id <> 0
) wfSchema
INNER JOIN (SELECT DISTINCT mcp.subject_id,
mcp.mapping_id,
mcp.from_object_name,
CAST(wf.expression AS VARCHAR(MAX)) AS EXPRESSION,
wi.widget_type_name AS Remarks,
wf.field_name AS Column_Name
FROM rep_mapping_conn_ports mcp,
rep_widget_inst wi,
rep_widget_field wf
WHERE (mcp.mapping_id = wi.mapping_id AND mcp.from_object_instance_id = wi.instance_id AND wi.widget_type_name = mcp.from_object_type_name)
AND (wi.widget_type NOT IN ( 1, 2, 3 ))
AND (wi.widget_id = wf.widget_id )
AND (wi.widget_type NOT IN ( 1, 2, 3 ))
AND (wi.widget_id = wf.widget_id )
AND (wf.field_name != CAST(wf.expression AS VARCHAR(MAX)))
)exp
ON exp.subject_id = wfSchema.subject_id
AND exp.mapping_id = wfSchema.mapping_id
Transformation Column Expression Query¶
SELECT
wfSchema.CATALOG,
wfSchema.subject_id,
wfSchema."SCHEMA",
attr1.instance_name AS TABLE_NAME,
attr1.widget_type,
attr1.widget_id,
attr1.attr_name AS REMARKS,
attr1.attr_value AS Condition
FROM
(
SELECT DISTINCT
c.subject_area AS CATALOG,
a.subject_id,
c.mapping_id,
Concat(Concat(a.task_name, '/'), c.mapping_name) AS "SCHEMA"
FROM
rep_all_tasks a,
rep_task_inst b,
rep_load_sessions c
WHERE
a.subject_id = c.subject_id
AND a.task_type = 71
AND a.task_id = b.workflow_id
AND b.task_type = 68
AND b.task_id = c.session_id
AND c.mapping_id <> 0
) wfSchema
INNER JOIN
(
SELECT
wi.mapping_id,
rls.mapping_name,
wi.widget_id,
instance_id,
wi.instance_name,
rls.subject_id,
rls.session_id,
attr.attr_name,
attr.attr_value,
attr.widget_type
FROM
opb_widget_inst wi,
rep_load_sessions rls,
(
SELECT
wa.widget_id,
wa.widget_type,
wa.attr_name,
CONVERT(varchar(MAX), wa.attr_value) AS attr_value
FROM
rep_widget_attr wa
WHERE
(wa.widget_type = 10 AND wa.attr_id = 1 AND wa.attr_type = 2 AND CAST(wa.attr_value AS varchar(MAX)) NOT IN ('0'))
OR
(wa.widget_type = 12 AND wa.attr_id = 3 AND wa.attr_type = 2 AND CAST(wa.attr_value AS varchar(MAX)) NOT IN ('0'))
OR
(wa.widget_type = 80 AND wa.attr_id = 8 AND wa.attr_type = 5 AND CAST(wa.attr_value AS varchar(MAX)) NOT IN ('0'))
) attr
WHERE
wi.widget_type in (10,12,80)
AND wi.mapping_id=rls.mapping_id
AND wi.widget_id=attr.widget_id
AND rls.subject_id!=9
) attr1
ON
attr1.subject_id = wfSchema.subject_id
AND attr1.mapping_id = wfSchema.mapping_id;
Connection Details Query¶
SELECT
rp.mapping_id,
rp.session_id,
CONCAT(si.workflow_name,CONCAT('/',rp.mapping_name)) AS TARGET_SCHEMA,
rp.subject_area AS CATALOG,
rp.subject_id,
wi.widget_type,
wi.widget_type_name,
wi.instance_id,
wi.instance_name AS TABLE_NAME,
ati.attr_value AS SOURCE_SCHEMA,
si.connection_name
FROM rep_load_sessions rp,
rep_widget_inst wi,
(SELECT case when is_target=0 then 1 else 2 end AS widget_type,
si.subject_area,
si.subject_id,
si.connection_name,
si.workflow_name,
si.session_id
FROM rep_session_instances si
) si,
(SELECT
sat.session_id,
sat.attr_value,
sat.attr_id ,
case when sat.attr_id=3 then 2
when sat.attr_id=28 then 1
end AS widget_type,
sat.sess_widg_inst_id,
inst.instance_id
FROM opb_swidget_attr sat,
opb_swidget_inst inst
WHERE sat.session_id=inst.session_id
AND sat.sess_widg_inst_id=inst.sess_widg_inst_id
AND sat.attr_id in(3,28)) ati
WHERE (rp.mapping_id=wi.mapping_id AND rp.subject_id=wi.subject_id)
AND (rp.session_id=si.session_id AND rp.subject_id=si.subject_id)
AND (rp.session_id=ati.session_id AND wi.instance_id=ati.instance_id)
AND wi.widget_type=si.widget_type
AND wi.widget_type in (1,2)