Troubleshooting¶

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Core Connector Core connectors are included with all Alation platform tiers (subject to each tier’s connector limits) and are fully supported by 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)

Troubleshooting¶

Oracle Database Queries¶

Filter Folder List Query¶

Schema Query¶

Table Query¶

Column Query¶

Foreign Key Query¶

Transformation Expression and Condition Query¶

Verify Data (Transformation Expression)¶

Connection Details Query¶

Schema Name for Source and Target¶

SQL Server Database Queries¶

Filter Folder List Query¶

Schema Query¶

Table Query¶

Column Query¶

Foreign Key Query¶

Transformation Column Expression Query¶

Transformation Column Expression Query¶

Connection Details Query¶

Alation User Documentation PDFs