Ich habe es mal zusammengeklickt:
SELECT
obj_main.isys_obj__id AS '__id__',
obj_main.isys_obj__title AS 'Schrank',
obj_main.isys_obj__sysid AS 'SYS-ID',
SUM(
IF(
!ISNULL(
isys_catg_location_list__pos
)
AND isys_catg_location_list__pos != 0, 1, 0
)
) AS 'Anzahl Elemente',
CONCAT(
IF(
!ISNULL(rack.isys_catg_formfactor_list__rackunits),
rack.isys_catg_formfactor_list__rackunits - SUM(
IF(
ISNULL(
isys_catg_location_list__pos
)
OR isys_catg_location_list__pos = 0
, 0, dev.isys_catg_formfactor_list__rackunits
)
),
rack.isys_catg_formfactor_list__rackunits
)
, ' HE'
) AS 'Verbleibende Kapazitaet',
j2.isys_catg_location_list__parentid AS 'isys_cmdb_dao_category_g_location::dynamic_property_callback_location_path::isys_catg_location_list__parentid::LC__CMDB__CATG__LOCATION_PATH'
FROM isys_obj AS obj_main
INNER JOIN isys_cmdb_status AS obj_main_status ON obj_main_status.isys_cmdb_status__id = obj_main.isys_obj__isys_cmdb_status__id
INNER JOIN isys_obj_type ON isys_obj_type__id = isys_obj__isys_obj_type__id
INNER JOIN isys_catg_formfactor_list rack ON rack.isys_catg_formfactor_list__isys_obj__id = isys_obj__id
LEFT JOIN isys_catg_location_list AS j2 ON obj_main.isys_obj__id = j2.isys_catg_location_list__isys_obj__id
LEFT JOIN isys_obj AS j3 ON j3.isys_obj__id = j2.isys_catg_location_list__isys_obj__id
LEFT JOIN isys_obj_type AS j4 ON j4.isys_obj_type__id = obj_main.isys_obj__isys_obj_type__id
LEFT JOIN isys_catg_formfactor_list dev ON dev.isys_catg_formfactor_list__isys_obj__id = isys_catg_location_list__isys_obj__id
WHERE TRUE
AND ( (j4.isys_obj_type__id != '60' ) AND (j4.isys_obj_type__id != '63' ) )
ORDER BY obj_main.isys_obj__title DESC
