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