Cable Locations Report Query

This AdHoc Query was created to replace the Cable Locations Report when it was removed from the built-in reports. This AdHoc has notable performance improvements on the original report. This report will display all Locations for all Cable items. This includes from and to different Locations and also from and to different Equipment.

Cable Locations Report for Oracle and MySQL

Cable Locations Report

-- This query is valid for MySQL AND Oracle
SELECT
    C.RECID,
    C.NAME,
    CL.VALUE AS C_CABLE_TYPE_VALUE,
    ORIGIN_EQUIPMENT.FROM_EQP_TYPE,
    ORIGIN_EQUIPMENT.FROM_EQP,
    ORIGIN_EQUIPMENT.FROM_LOC,
    DESTINATION_PAIRS.LOW_PAIR,
    DESTINATION_PAIRS.HIGH_PAIR,
    DESTINATION_EQUIPMENT.TO_EQP_TYPE,
    DESTINATION_EQUIPMENT.TO_EQP,
    DESTINATION_EQUIPMENT.TO_LOC,
    C.SIGNAL_LOSS,
    C.LENGTH
FROM CABLES C
LEFT JOIN LISTS CL ON CL.RECID = C.CABLE_TYPE_LISTS_RECID
LEFT JOIN (
    SELECT
        CE.CABLES_RECID,
        LTP.PATH AS FROM_LOC,
        COALESCE(E.EQUIPMENT_ID, E.ASSET_TAG, NULL) AS FROM_EQP,
        L.VALUE AS FROM_EQP_TYPE
    FROM CABLES_EQUIPMENT CE
    LEFT JOIN EQUIPMENT E ON CE.EQUIPMENT_RECID = E.RECID
    LEFT JOIN LISTS L ON L.RECID = E.CABLING_EQP_TYPE_LISTS_RECID
    LEFT JOIN LOCATIONS_TEXTPATH LTP ON E.LOCATIONS_RECID = LTP.RECID
    WHERE CE.FROM_EQUIPMENT_FLAG = 1
)ORIGIN_EQUIPMENT ON ORIGIN_EQUIPMENT.CABLES_RECID = C.RECID
LEFT JOIN (
    SELECT
        CE.CABLES_RECID,
        CE.EQUIPMENT_RECID,
        LTP.PATH AS TO_LOC,
        COALESCE(E.EQUIPMENT_ID, E.ASSET_TAG, NULL) AS TO_EQP,
        L.VALUE AS TO_EQP_TYPE
    FROM CABLES_EQUIPMENT CE
    LEFT JOIN EQUIPMENT E ON CE.EQUIPMENT_RECID = E.RECID
    LEFT JOIN LISTS L ON L.RECID = E.CABLING_EQP_TYPE_LISTS_RECID
    LEFT JOIN LOCATIONS_TEXTPATH LTP ON E.LOCATIONS_RECID = LTP.RECID
    WHERE CE.FROM_EQUIPMENT_FLAG = 0
) DESTINATION_EQUIPMENT ON DESTINATION_EQUIPMENT.CABLES_RECID = C.RECID
LEFT JOIN (
    SELECT
        PSE.TO_EQUIPMENT_RECID,
        MIN(PS.NUM) AS LOW_PAIR,
        MAX(PS.NUM) AS HIGH_PAIR
    FROM PAIRS_STRANDS_TO_EQP PSE
    LEFT JOIN PAIRS_STRANDS PS ON PS.RECID = PSE.PAIRS_STRANDS_RECID
    GROUP BY PSE.TO_EQUIPMENT_RECID
) DESTINATION_PAIRS ON DESTINATION_PAIRS.TO_EQUIPMENT_RECID = DESTINATION_EQUIPMENT.EQUIPMENT_RECID
GROUP BY
    C.RECID,
    C.NAME,
    CL.VALUE,
    C.SIGNAL_LOSS,
    C.LENGTH,
    ORIGIN_EQUIPMENT.FROM_EQP_TYPE,
    ORIGIN_EQUIPMENT.FROM_EQP,
    ORIGIN_EQUIPMENT.FROM_LOC,
    DESTINATION_EQUIPMENT.TO_EQP_TYPE,
    DESTINATION_EQUIPMENT.TO_EQP,
    DESTINATION_EQUIPMENT.TO_LOC,
    DESTINATION_PAIRS.LOW_PAIR,
    DESTINATION_PAIRS.HIGH_PAIR