Find objects and object type attributes with database scripts

When you want to find objects and object type attributes, you can use database scripts. Here are some examples of when this might be useful.

Same value multiple times

Find all object attribute values that have more than one value (the same value) for the object attribute value which is always incorrect.

PostgreSQL
SELECT
  O."ID",
  OTA."NAME",
  OA."ID",
  OA."OBJECT_TYPE_ATTRIBUTE_ID",
  OA."OBJECT_ID",
  count(OAV."ID"),
  OAV."TEXT_VALUE",
  OAV."INTEGER_VALUE",
  OAV."BOOLEAN_VALUE",
  OAV."DATE_VALUE",
  OAV."REFERENCED_OBJECT_ID"
FROM "AO_8542F1_IFJ_OBJ" O
  LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_ATTR" OA ON O."ID" = OA."OBJECT_ID"
  LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_TYPE_ATTR" OTA ON OTA."ID" = OA."OBJECT_TYPE_ATTRIBUTE_ID"
  LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_ATTR_VAL" OAV ON OA."ID" = OAV."OBJECT_ATTRIBUTE_ID"
GROUP BY O."ID", OTA."NAME", OA."ID", OA."OBJECT_TYPE_ATTRIBUTE_ID", OA."OBJECT_ID", OAV."TEXT_VALUE",
  OAV."INTEGER_VALUE", OAV."BOOLEAN_VALUE", OAV."DATE_VALUE", OAV."REFERENCED_OBJECT_ID"
HAVING count(OAV."ID") > 1; 

Maximum cardinality violation (find objects)

Find all objects that have one or more values that violate the maximum constraint for at least one object type attribute configured for the object. This might be a fine result since decreasing the maximum cardinality without changing the objects in Insight will cause this to be the expected behavior.

PostgreSQL
SELECT
  O."ID",
  O."OBJECT_KEY",
  O."LABEL"
FROM "AO_8542F1_IFJ_OBJ" O
  LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_ATTR" OA ON O."ID" = OA."OBJECT_ID"
WHERE OA."ID" IN (SELECT OA."ID"
                  FROM "AO_8542F1_IFJ_OBJ_ATTR" OA
                    LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_TYPE_ATTR" OTA ON OTA."ID" = OA."OBJECT_TYPE_ATTRIBUTE_ID"
                    LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_ATTR_VAL" OAV ON OA."ID" = OAV."OBJECT_ATTRIBUTE_ID"
                  WHERE OTA."MAXIMUM_CARDINALITY" != -1
                  GROUP BY OTA."ID", OA."ID"
                  HAVING count(*) > OTA."MAXIMUM_CARDINALITY");

Maximum cardinality violation (find objects type attributes)

Find all object type attributes with more values than cardinality specifies. This might not be a problem if the maximum cardinality is changed without updating all objects.

PostgreSQL
SELECT
  OTA."ID",
  OA."ID",
  count(*)
FROM "AO_8542F1_IFJ_OBJ_ATTR" OA
  LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_TYPE_ATTR" OTA ON OTA."ID" = OA."OBJECT_TYPE_ATTRIBUTE_ID"
  LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_ATTR_VAL" OAV ON OA."ID" = OAV."OBJECT_ATTRIBUTE_ID"
WHERE OTA."MAXIMUM_CARDINALITY" != -1
GROUP BY OTA."ID", OA."ID"
HAVING count(*) > OTA."MAXIMUM_CARDINALITY";
Last modified on Oct 14, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.