/* invalid EO principal/sub relationships (records are excluded from export)
Structural problems with a EO record group: 
  - EO references itself as its own principal EO
  - a three-level principal EO/sub EO relationship is defined; this gets at most of the following, at least usually
    - EOs in record group do not all reference the same principal EO record
    - all EOs in record group reference a principal EO record
    - multiple EOs in record group do not reference a principal EO
    - at least one EO in record group refers a principal EO not found in group
*/

--UNION query to combine different issues:
--EO references itself as its own principal EO

SELECT getnametypecd(sname_id) type, es.element_subnational_id 
  , getsciname(es.sname_id) sname, est_elcode(es.element_subnational_id) elcode
  , eo.eo_num, eoP.eo_num principal_eo_num, Null Grand_eo_num 
  --, eo.do_not_exchange_ind, eoP.do_not_exchange_ind principal_DNE
  , eo.eo_id, eoP.eo_id principal_eo_id, Null Grand_eo_id
  , eo.shape_id, eo.principal_eo_shape_id, Null Grand_shape_id
  , 'self-referencing' problem
FROM eo, eo eoP, element_subnational es
WHERE eo.principal_eo_shape_id = eoP.shape_id
  and eo.element_subnational_id = es.element_subnational_id 
  and eoP.eo_id = eo.eo_id
--ORDER BY type, elcode, sname, eoP.eo_id nulls first, eo.eo_id;
UNION
--principal EO has its own principal EO (EO nesting of more than 2 levels)
SELECT getnametypecd(sname_id) type, es.element_subnational_id
  , getsciname(es.sname_id) sname, est_elcode(es.element_subnational_id) elcode
  , eo.eo_num, eoP.eo_num principal_eo_num, eoGP.eo_num Grand_eo_num 
  , eo.eo_id, eoP.eo_id principal_eo_id, eoGP.eo_id Grand_eo_id
  , eo.shape_id, eo.principal_eo_shape_id, eoP.principal_eo_shape_id Grand_shape_id
  , 'has grand-EO' problem
FROM eo, eo eoP, eo eoGP, element_subnational es
WHERE eo.principal_eo_shape_id = eoP.shape_id 
  and eoP.principal_eo_shape_id = eoGP.shape_id 
  and eo.element_subnational_id = es.element_subnational_id 
  and eoP.eo_id != eo.eo_id
--ORDER BY type, elcode, sname, eoP.eo_id nulls first, eo.eo_id;
UNION
--EOs with shape or principal shape equal to a grand-EO
SELECT getnametypecd(sname_id) type, es.element_subnational_id
  , getsciname(es.sname_id) sname, est_elcode(es.element_subnational_id) elcode
  , eo.eo_num, eoP.eo_num principal_eo_num, Null Grand_eo_num 
  , eo.eo_id, eoP.eo_id principal_eo_id, Null Grand_eo_id
  , eo.shape_id, eo.principal_eo_shape_id, Null Grand_shape_id 
  , 'EO or principal EO is grand-EO of another EO' problem
FROM eo, eo eoP, element_subnational es
WHERE eo.principal_eo_shape_id = eoP.shape_id(+) 
  and eo.element_subnational_id = es.element_subnational_id 
  and (eo.shape_id in(select eoGP2.shape_id from eo eo2, eo eoP2, eo eoGP2 where eo2.principal_eo_shape_id = eoP2.shape_id and eoP2.principal_eo_shape_id = eoGP2.shape_id)
       or eo.principal_eo_shape_id in(select eoGP2.shape_id from eo eo2, eo eoP2, eo eoGP2 where eo2.principal_eo_shape_id = eoP2.shape_id and eoP2.principal_eo_shape_id = eoGP2.shape_id))
ORDER BY type, elcode, sname, Grand_eo_id, principal_eo_id nulls first, eo_id