Loop through list of fields, check against lookup table

3 votes
2 answers
5873 views
                          I have a list of fields:

### FIELD_DOMAIN_ENG_VW
    +-------------+------------+-------------+
    | TABLE_NAME  | FIELD_NAME | DOMAIN_NAME |
    +-------------+------------+-------------+
    | ENG.TABLE_1 | FIELD_1    | DOMAIN_ABC  |
    | ENG.TABLE_1 | FIELD_2    | DOMAIN_XYZ  |
    | ENG.TABLE_2 | FIELD_1    | DOMAIN_XYZ  |
    +-------------+------------+-------------+

The view looks at all the tables in a geodatabase , and lists any fields that have a domain  associated with them (a domain is the GIS equivalent of a lookup table/validation table).

The underlying tables look like this:

### TABLE_1
    +--------------+--------------+
    | FIELD_1      | FIELD_2      |
    | {DOMAIN_ABC} | {DOMAIN_XYZ} |
    +--------------+--------------+
    | A            | X            |
    | B            | Y            |
    | C            | zzzz         |
    | BLACK SHEEP  |              |
    +--------------+--------------+

### TABLE_2
    +--------------+--------------+
    | FIELD_1      | FIELD_2      |
    | {DOMAIN_XYZ} |              |
    +--------------+--------------+
    | Z            | ...          |
    | Y            |              |
    | X            |              |
    | asdf         |              |
    +--------------+--------------+

The domains look like this:

### DOMAIN_VALUES_VW
    +------------+------+-------------+
    | DOMAIN     | CODE | DESCRIPTION |
    +------------+------+-------------+
    | DOMAIN_ABC | A    | EH          |
    | DOMAIN_ABC | B    | BEE         |
    | DOMAIN_ABC | C    | SEE         |
    +------------+------+-------------+
    | DOMAIN_XYZ | X    | EX          |
    | DOMAIN_XYZ | Y    | WHY         |
    | DOMAIN_XYZ | Z    | ZEE         |
    +------------+------+-------------+

The source is an xml column  in a single system table ; I've extracted  all the domains into this view.

## Question

For validation purposes, I have made a query that will check if there are values in a field that do not match the corresponding domain:

    INSERT INTO ENG.CV_ERRORS
    (TABLE_NAME, FIELD_NAME, ERROR)
    SELECT
        'TABLE_1' AS TABLE_NAME
        ,'FIELD_1' AS FIELD_NAME
        ,FIELD_1 AS ERROR
    FROM 
        ENG.TABLE_1 
        LEFT JOIN 
            (
            SELECT CODE
            FROM  INFRASTR.D_CV_ENG_VW
            WHERE DOMAIN = 'DOMAIN_ABC'
            )
            ON FIELD_1 = CODE
    WHERE 
        FIELD_1 IS NOT NULL
        AND CODE IS NULL



    +------------+------------+-------------+
    | TABLE_NAME | FIELD_NAME | ERROR       |
    +------------+------------+-------------+
    | TABLE_1    | FIELD_1    | BLACK SHEEP |
    +------------+------------+-------------+

However, this query is hardcoded to be run on a single field, in a single table at a time. I need to check all of the fields with domains, in all of the tables in the database - programmatically.

How can I do this? I'm pretty sure this can be done with PL/SQL and dynamic SQL, but I'm so new to PL/SQL that it is proving to be rather difficult. 





                        
Asked by User1974 (1527 rep)
Jan 3, 2017, 05:54 PM
Last activity: Mar 29, 2017, 02:07 AM
Loop through list of fields, check against lookup table

Related Questions
