Atrium CMDB: List of regular forms for a Class

Version 2
    Share This:

    Some (SQL) updates are not allowed on join forms, so it was necessary to figure out, which regular forms are involved in the CMDB class join.

     

    SELECT CAST(ROW_NUMBER() OVER(
              ORDER BY classdef.request_id,
                       o.request_id) AS VARCHAR) AS requestid,
              CLASSDEF.Class_Name,
              CLASSDEF.class_instanceid,
              CASE
                  WHEN o.Class_Name = 'BMC_BaseElement'
                  THEN concat(o.namespace, ':', o.Class_Name)
                  ELSE concat(o.namespace, ':', o.Class_Name, '_')
              END AS FORMNAME,
      (select schemaid from arschema a where
      overlayProp in (0,2,4) AND
      name  = CASE
    WHEN o.Class_Name = 'BMC_BaseElement'
    THEN concat(o.namespace, ':', o.Class_Name)
    ELSE concat(o.namespace, ':', o.Class_Name, '_')
    END) as schemaid
       FROM
       (
           SELECT o1.Request_ID,
                  o1.class_name,
                  instanceId AS class_instanceid,
                  concat('|', o1.ClassID01, '|', o1.ClassID02, '|', o1.ClassID03, '|', o1.ClassID04, '|', o1.ClassID05, '|', o1.ClassID06, '|', o1.ClassID07, '|', o1.ClassID08, '|', o1.ClassID09, '|', o1.classid10) AS CLASSPARENTS
           FROM OBJSTR_Class o1
           WHERE o1.class_type = 'Class'
                 AND o1.CLASSID01 = 'BMC_ASSETBASE'
       ) CLASSDEF,
       OBJSTR_Class o
       WHERE CLASSDEF.CLASSPARENTS LIKE '%|' + o.instanceid + '|%'