Posts made by beckmann

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