Published at October 26th, 2025 • Last updated 9 days ago

SQL validation for views

USER_DATA

SQL Description Expected result
select USER_NAME, count(USER_NAME) from USER_DATA group by USER_NAME having count(USER_NAME) > 1;
Verification of dublicate user_names This sql should return 0 rows.
select EMAIL, count(EMAIL) from USER_DATA group by EMAIL having count(EMAIL) > 1;
Verification of dublicate emails_names This sql should return 0 rows.

 

PERSON_DATA

SQL Description Expected result
select PERSON_ID, count(PERSON_ID) from PERSON_DATA group by PERSON_ID having count(PERSON_ID) > 1;
Verification of dublicate person_ids. This SQL should return 0 rows.
select distinct PERSON_DATA.PERSON_ID
FROM PERSON_DATA
 LEFT OUTER JOIN STAFF_ORG_RELATION ON PERSON_DATA.PERSON_ID = STAFF_ORG_RELATION.PERSON_ID
 LEFT OUTER JOIN STUDENT_ORG_RELATION ON PERSON_DATA.PERSON_ID = STUDENT_ORG_RELATION.PERSON_ID
 LEFT OUTER JOIN VISITING_SCHOLAR_ORG_RELATION ON PERSON_DATA.PERSON_ID = VISITING_SCHOLAR_ORG_RELATION.PERSON_ID
 LEFT OUTER JOIN HONORARY_STAFF_ORG_RELATION ON PERSON_DATA.PERSON_ID = HONORARY_STAFF_ORG_RELATION.PERSON_ID
WHERE
 STAFF_ORG_RELATION.ID IS NULL
 AND STUDENT_ORG_RELATION.ID IS NULL
 AND VISITING_SCHOLAR_ORG_RELATION.ID IS NULL
 AND HONORARY_STAFF_ORG_RELATION.ID IS NULL ;
Finds all persons that do not have any organisation relations available. This SQL should return 0 rows.
SELECT PERSON_DATA.PERSON_ID, COUNT(PERSON_DATA.PERSON_ID) FROM PERSON_DATA
JOIN (
SELECT STAFF_ORG_RELATION.PERSON_ID FROM STAFF_ORG_RELATION WHERE PRIMARY_ASSOCIATION IS TRUE
UNION ALL
SELECT STUDENT_ORG_RELATION.PERSON_ID FROM STUDENT_ORG_RELATION WHERE PRIMARY_ASSOCIATION IS TRUE
UNION ALL
SELECT VISITING_SCHOLAR_ORG_RELATION.PERSON_ID FROM VISITING_SCHOLAR_ORG_RELATION WHERE PRIMARY_ASSOCIATION IS TRUE
UNION ALL
SELECT HONORARY_STAFF_ORG_RELATION.PERSON_ID FROM HONORARY_STAFF_ORG_RELATION WHERE PRIMARY_ASSOCIATION IS TRUE
) A ON A.PERSON_ID=PERSON_DATA.PERSON_ID
GROUP BY PERSON_DATA.PERSON_ID
HAVING COUNT(PERSON_DATA.PERSON_ID)>1
Finds all person_ids that have organisation relations that have more than one primary association. This SQL should return 0 rows.
SELECT PERSON_DATA.PERSON_ID, PERSON_DATA.USER_ID FROM PERSON_DATA WHERE NOT PERSON_DATA.USER_ID IS NULL AND USER_ID NOT IN (SELECT ID FROM USER_DATA)
Finds all persons that have relations to users that doesn't exist in the USER_DATA. This SQL should return 0 rows.

 

ORG_RELATION

SQL Description Expected result
SELECT ID, COUNT(id) from (
 SELECT ID from STAFF_ORG_RELATION
 union all select id from STUDENT_ORG_RELATION
 union all select id from VISITING_SCHOLAR_ORG_RELATION
 union all select id from HONORARY_STAFF_ORG_RELATION
 ) as result group by ID having count(ID) > 1;
Verification of duplicate ids across relation tables
 		 This SQL should return 0 rows.

 

STAFF_ORG_RELATION

SQL Description Expected result
select ID, count(ID) from STAFF_ORG_RELATION group by ID having count(ID) > 1;
Verification of duplicate ids This SQL should return 0 rows.
SELECT ID, COUNT(id) from (
 SELECT ID from STAFF_ORG_RELATION
 union all select id from STUDENT_ORG_RELATION
 union all select id from VISITING_SCHOLAR_ORG_RELATION
 union all select id from HONORARY_STAFF_ORG_RELATION
 ) as result group by ID having count(ID) > 1;
Verification of duplicate ids across relation tables
 		 This SQL should return 0 rows.

 

STUDENT_ORG_RELATION

SQL Description Expected result
select ID, count(ID) from STUDENT_ORG_RELATION group by ID having count(ID) > 1;
Verification of duplicate ids This SQL should return 0 rows.
select * from STUDENT_ORG_RELATION as s
    left join organisation_data as o1 on (o1.organisation_id = s.org_source_id)
    left join organisation_data as o2 on (o2.organisation_id = s.org_id)
    left join organisation_data as o3 on (o3.organisation_id = s.org_pure_id)
    left join organisation_data as o4 on (o4.organisation_id = s.org_classified_ids_id)
    where o1.organisation_id is null AND o2.organisation_id is null
    AND o3.organisation_id is null AND o4.organisation_id is null;
Verifies that organisation data matches organisation relations This SQL should return 0 rows.

 

VISITING_SCHOLAR_ORG_RELATION

SQL Description Expected result
select ID, count(ID) from VISITING_ORG_RELATION group by ID having count(ID) > 1;
Verification of duplicate ids This SQL should return 0 rows.
select * from VISITING_ORG_RELATION as s
    left join organisation_data as o1 on (o1.organisation_id = s.org_source_id)
    left join organisation_data as o2 on (o2.organisation_id = s.org_id)
    left join organisation_data as o3 on (o3.organisation_id = s.org_pure_id)
    left join organisation_data as o4 on (o4.organisation_id = s.org_classified_ids_id)
    where o1.organisation_id is null AND o2.organisation_id is null
    AND o3.organisation_id is null AND o4.organisation_id is null;
Verifies that organisation data matches organisation relations This SQL should return 0 rows.

 

HONORARY_STAFF_ORG_RELATION

SQL Description Expected result
select ID, count(ID) from HONORARY_STAFF_ORG_RELATION group by ID having count(ID) > 1;
Verification of duplicate ids This SQL should return 0 rows.
select * from HONORARY_STAFF_ORG_RELATION as s
    left join organisation_data as o1 on (o1.organisation_id = s.org_source_id)
    left join organisation_data as o2 on (o2.organisation_id = s.org_id)
    left join organisation_data as o3 on (o3.organisation_id = s.org_pure_id)
    left join organisation_data as o4 on (o4.organisation_id = s.org_classified_ids_id)
    where o1.organisation_id is null AND o2.organisation_id is null
    AND o3.organisation_id is null AND o4.organisation_id is null;
Verifies that organisation data matches organisation relations This SQL should return 0 rows.

 

ORG_RELATION_ADDRESSES

SQL Description Expected result
select ID, count(ID) from ORG_RELATION_ADDRESSES group by ID having count(ID) > 1;
Verification of duplicate ids This SQL should return 0 rows.
SELECT ID, STAFF_RELATION_ID, STUDENT_RELATION_ID, VISITOR_RELATION_ID, HONORARY_RELATION_ID FROM ORG_RELATION_ADDRESSES
WHERE STAFF_RELATION_ID IS NOT NULL AND STAFF_RELATION_ID NOT IN (SELECT ID FROM STAFF_ORG_RELATION) OR
STUDENT_RELATION_ID IS NOT NULL AND STUDENT_RELATION_ID NOT IN (SELECT ID FROM STUDENT_ORG_RELATION) OR
VISITOR_RELATION_ID IS NOT NULL AND VISITOR_RELATION_ID NOT IN (SELECT ID FROM VISITING_SCHOLAR_ORG_RELATION) OR
HONORARY_RELATION_ID IS NOT NULL AND HONORARY_RELATION_ID NOT IN (SELECT ID FROM HONORARY_STAFF_ORG_RELATION)
Verification of relations to STAFF_ORG_RELATION, STUDENT_ORG_RELATION, VISITING_SCHOLAR_ORG_RELATION and HONORARY_STAFF_ORG_RELATION.  

 

ORG_RELATION_PHONE_NUMBERS

SQL Description Expected result
select ID, count(ID) from ORG_RELATION_PHONE_NUMBERS group by ID having count(ID) > 1;
Verification of dublicate ids This SQL should return 0 rows.
SELECT ID, STAFF_RELATION_ID, STUDENT_RELATION_ID, VISITOR_RELATION_ID, HONORARY_RELATION_ID FROM ORG_RELATION_PHONE_NUMBERS
WHERE STAFF_RELATION_ID IS NOT NULL AND STAFF_RELATION_ID NOT IN (SELECT ID FROM STAFF_ORG_RELATION) OR
STUDENT_RELATION_ID IS NOT NULL AND STUDENT_RELATION_ID NOT IN (SELECT ID FROM STUDENT_ORG_RELATION) OR
VISITOR_RELATION_ID IS NOT NULL AND VISITOR_RELATION_ID NOT IN (SELECT ID FROM VISITING_SCHOLAR_ORG_RELATION) OR
HONORARY_RELATION_ID IS NOT NULL AND HONORARY_RELATION_ID NOT IN (SELECT ID FROM HONORARY_STAFF_ORG_RELATION)
Verification of relations to STAFF_ORG_RELATION, STUDENT_ORG_RELATION, VISITING_SCHOLAR_ORG_RELATION and HONORARY_STAFF_ORG_RELATION. This SQL should return 0 rows.

 

ORG_RELATION_EMAILS

SQL Description Expected result
select ID, count(ID) from ORG_RELATION_EMAILS group by ID having count(ID) > 1;
Verification of duplicate ids This SQL should return 0 rows.
SELECT ID, STAFF_RELATION_ID, STUDENT_RELATION_ID, VISITOR_RELATION_ID, HONORARY_RELATION_ID FROM ORG_RELATION_EMAILS
WHERE STAFF_RELATION_ID IS NOT NULL AND STAFF_RELATION_ID NOT IN (SELECT ID FROM STAFF_ORG_RELATION) OR
STUDENT_RELATION_ID IS NOT NULL AND STUDENT_RELATION_ID NOT IN (SELECT ID FROM STUDENT_ORG_RELATION) OR
VISITOR_RELATION_ID IS NOT NULL AND VISITOR_RELATION_ID NOT IN (SELECT ID FROM VISITING_SCHOLAR_ORG_RELATION) OR
HONORARY_RELATION_ID IS NOT NULL AND HONORARY_RELATION_ID NOT IN (SELECT ID FROM HONORARY_STAFF_ORG_RELATION)
Verification of relations to STAFF_ORG_RELATION, STUDENT_ORG_RELATION, VISITING_SCHOLAR_ORG_RELATION and HONORARY_STAFF_ORG_RELATION.  

 

ORG_RELATION_WEB_ADDRESSES

SQL Description Expected result
select ID, count(ID) from ORG_RELATION_WEB_ADDRESSES group by ID having count(ID) > 1;
Verification of duplicate ids This SQL should return 0 rows.
SELECT ID, STAFF_RELATION_ID, STUDENT_RELATION_ID, VISITOR_RELATION_ID, HONORARY_RELATION_ID FROM ORG_RELATION_WEB_ADDRESSES
WHERE STAFF_RELATION_ID IS NOT NULL AND STAFF_RELATION_ID NOT IN (SELECT ID FROM STAFF_ORG_RELATION) OR
STUDENT_RELATION_ID IS NOT NULL AND STUDENT_RELATION_ID NOT IN (SELECT ID FROM STUDENT_ORG_RELATION) OR
VISITOR_RELATION_ID IS NOT NULL AND VISITOR_RELATION_ID NOT IN (SELECT ID FROM VISITING_SCHOLAR_ORG_RELATION) OR
HONORARY_RELATION_ID IS NOT NULL AND HONORARY_RELATION_ID NOT IN (SELECT ID FROM HONORARY_STAFF_ORG_RELATION)
Verification of relations to STAFF_ORG_RELATION, STUDENT_ORG_RELATION, VISITING_SCHOLAR_ORG_RELATION and HONORARY_STAFF_ORG_RELATION.  

 

ORG_RELATION_KEYWORDS

SQL Description Expected result
SELECT STAFF_RELATION_ID, STUDENT_RELATION_ID, VISITOR_RELATION_ID, HONORARY_RELATION_ID FROM ORG_RELATION_KEYWORDS
WHERE STAFF_RELATION_ID IS NOT NULL AND STAFF_RELATION_ID NOT IN (SELECT ID FROM STAFF_ORG_RELATION) OR
STUDENT_RELATION_ID IS NOT NULL AND STUDENT_RELATION_ID NOT IN (SELECT ID FROM STUDENT_ORG_RELATION) OR
VISITOR_RELATION_ID IS NOT NULL AND VISITOR_RELATION_ID NOT IN (SELECT ID FROM VISITING_SCHOLAR_ORG_RELATION) OR
HONORARY_RELATION_ID IS NOT NULL AND HONORARY_RELATION_ID NOT IN (SELECT ID FROM HONORARY_STAFF_ORG_RELATION)
Verification of relations to STAFF_ORG_RELATION, STUDENT_ORG_RELATION, VISITING_SCHOLAR_ORG_RELATION and HONORARY_STAFF_ORG_RELATION. This SQL should return 0 rows.

 

PERSON_IDS

SQL Description Expected result
SELECT * FROM PERSON_IDS WHERE PERSON_IDS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_IDS that are not related to a person in PERSON_DATA This SQL should return 0 rows.
SELECT ID, COUNT(ID) FROM PERSON_IDS GROUP BY ID HAVING COUNT(ID) > 1
Verify that the IDs are unique This SQL should return 0 rows.

 

PERSON_NAMES

SQL Description Expected result
SELECT * FROM PERSON_NAMES WHERE PERSON_NAMES.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_NAMES that are not related to a person in PERSON_DATA. This SQL should return 0 rows.
SELECT ID, COUNT(ID) FROM PERSON_NAMES GROUP BY ID HAVING COUNT(ID) > 1


 

 Verify that the IDs are unique This SQL should return 0 rows.

 

PERSON_TITLES

SQL Description Expected result
SELECT * FROM PERSON_NAMES WHERE PERSON_NAMES.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_NAMES that are not related to a person in PERSON_DATA. This SQL should return 0 rows.
SELECT ID, COUNT(ID) FROM PERSON_NAMES GROUP BY ID HAVING COUNT(ID) > 1


 

 Verify that the IDs are unique This SQL should return 0 rows.

 

PERSON_PROFILE_INFORMATION

SQL Description Expected result
SELECT * FROM PERSON_PROFILE_INFORMATION WHERE PERSON_PROFILE_INFORMATION.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_PROFILE_INFORMATION that are not related to a person in PERSON_DATA. This SQL should return 0 rows.
SELECT ID, COUNT(ID) FROM PERSON_PROFILE_INFORMATION GROUP BY ID HAVING COUNT(ID) > 1
Verify that the IDs are unique This SQL should return 0 rows.

 

PERSON_LEAVE_OF_ABSENCE

SQL Description Expected result
SELECT * FROM PERSON_LEAVE_OF_ABSENCE WHERE PERSON_LEAVE_OF_ABSENCE.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_LEAVE_OF_ABSENCE that are not related to a person in PERSON_DATA. This SQL should return 0 rows.
SELECT ID, COUNT(ID) FROM PERSON_LEAVE_OF_ABSENCE GROUP BY ID HAVING COUNT(ID) > 1
Verify that the IDs are unique  

 

PERSON_DOCUMENTS

SQL Description Expected result
SELECT * FROM PERSON_DOCUMENTS WHERE PERSON_DOCUMENTS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_DOCUMENTS that are not related to a person in PERSON_DATA. This SQL should return 0 rows.
SELECT ID, COUNT(ID) FROM PERSON_DOCUMENTS GROUP BY ID HAVING COUNT(ID) > 1
Verify that the IDs are unique  

 

PERSON_PHOTOS

SQL Description Expected result
SELECT * FROM PERSON_PHOTOS WHERE PERSON_PHOTOS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_PHOTOS that are not related to a person in PERSON_DATA. This SQL should return 0 rows.
SELECT ID, COUNT(ID) FROM PERSON_PHOTOS GROUP BY ID HAVING COUNT(ID) > 1
Verify that the IDs are unique This SQL should return 0 rows.

 

PERSON_EDUCATIONS

SQL Description Expected result
SELECT * FROM PERSON_EDUCATIONS WHERE PERSON_EDUCATIONS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_EDUCATIONS that are not related to a person in PERSON_DATA. This SQL should return 0 rows.
SELECT ID, COUNT(ID) FROM PERSON_EDUCATIONS GROUP BY ID HAVING COUNT(ID) > 1
Verify that the IDs are unique This SQL should return 0 rows.

 

PERSON_EXTERNAL_POSITIONS

SQL Description Expected result
SELECT * FROM PERSON_EXTERNAL_POSITIONS WHERE PERSON_EXTERNAL_POSITIONS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_EXTERNAL_POSITIONS that are not related to a person in PERSON_DATA. This SQL should return 0 rows.
SELECT ID, COUNT(ID) FROM PERSON_EXTERNAL_POSITIONS GROUP BY ID HAVING COUNT(ID) > 1
Verify that the IDs are unique This SQL should return 0 rows.

 

PERSON_PROF_QUALIFICATIONS

SQL Description Expected result
SELECT * FROM PERSON_PROF_QUALIFICATIONS WHERE PERSON_PROF_QUALIFICATIONS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_PROF_QUALIFICATIONS that are not related to a person in PERSON_DATA. This SQL should return 0 rows.
SELECT ID, COUNT(ID) FROM PERSON_PROF_QUALIFICATIONS GROUP BY ID HAVING COUNT(ID) > 1
Verify that the IDs are unique This SQL should return 0

 

SQL Description Expected result
SELECT * FROM PERSON_LINKS WHERE PERSON_LINKS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_LINKS that are not related to a person in PERSON_DATA. This SQL should return 0 rows.
SELECT ID, COUNT(ID) FROM PERSON_LINKS GROUP BY ID HAVING COUNT(ID) > 1
Verify that the IDs are unique This SQL should return 0

 

PERSON_KEYWORDS

SQL Description Expected result
SELECT * FROM PERSON_KEYWORDS WHERE PERSON_KEYWORDS.PERSON_ID NOT IN (SELECT PERSON_ID FROM PERSON_DATA)
Finds all entries in PERSON_KEYWORDS that are not related to a person in PERSON_DATA. This SQL should return 0 rows.

 

SUPERVISOR_DATA

SQL Description Expected result
select ID, count(ID) from HONORARY_STAFF_ORG_RELATION group by ID having count(ID) > 1;
Verification of duplicate ids This SQL should return 0 rows.
SELECT SUPERVISOR_DATA.* FROM SUPERVISOR_DATA
LEFT OUTER JOIN STAFF_ORG_RELATION ON SUPERVISOR_DATA.APPRENTICE_ORG_RELATION_ID = STAFF_ORG_RELATION.ID
 LEFT OUTER JOIN STUDENT_ORG_RELATION ON SUPERVISOR_DATA.APPRENTICE_ORG_RELATION_ID = STUDENT_ORG_RELATION.ID
 LEFT OUTER JOIN VISITING_SCHOLAR_ORG_RELATION ON SUPERVISOR_DATA.APPRENTICE_ORG_RELATION_ID = VISITING_SCHOLAR_ORG_RELATION.ID
 LEFT OUTER JOIN HONORARY_STAFF_ORG_RELATION ON SUPERVISOR_DATA.APPRENTICE_ORG_RELATION_ID = HONORARY_STAFF_ORG_RELATION.ID
WHERE
 STAFF_ORG_RELATION.ID IS NULL
 AND STUDENT_ORG_RELATION.ID IS NULL
 AND VISITING_SCHOLAR_ORG_RELATION.ID IS NULL
 AND HONORARY_STAFF_ORG_RELATION.ID IS NULL;
Verification of apprentice relations in supervisor_data view. Must return 0 rows This SQL should return 0 rows.

Was this article helpful?

Related articles