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 Assets 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 Sep 26, 2022
Powered by Confluence and Scroll Viewport.