.Cable Locations Report Query v2019.7

-- 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