Here is some SQL that you might find helpful:
SELECT ‘SELECT ”’ + C.RECNAME + ”’ AS RECNAME, OPRID FROM ‘ + D.TABLENAME +
‘ A WHERE NOT EXISTS (SELECT ”X” FROM PSOPRDEFN B WHERE A.OPRID = B.OPRID) UNION ‘
FROM PSRECFIELDALL C,
(SELECT RECNAME, SQLTABLENAME AS TABLENAME
FROM PSRECDEFN
WHERE SQLTABLENAME <> ‘ ‘
AND RECTYPE = 0
UNION
SELECT RECNAME, ‘PS_’ + RECNAME AS TABLENAME
FROM PSRECDEFN
WHERE SQLTABLENAME = ‘ ‘
AND RECTYPE = 0
) D
WHERE C.RECNAME = D.RECNAME
AND C.FIELDNAME = ‘OPRID’
This Query writes a huge SQL Statement that looks for rows in any table that has an OPRID that does not exist in the PSOPRDEFN table.
Here is how to use it:
- Run the SQL Statement
- Copy all of the rows as a new query
- Remove the last Union from the last line
- You might want to add “ORDER BY 1” at the end to get it in record order
- Execute the newly generated query
Note, this pattern can be replicated for other fields or situations too. It is not just limited to use with OPRID.