Converting Complicated Oracle Join Syntax
- by Grasper
I have asked for help before on porting joins of this nature, but nothing this complex.
I am porting a bunch of old SQL from oracle to postgres, which includes a lot of (+) style left joins. I need this in a format that pg will understand.
I am having trouble deciphering this join hierarchy:
SELECT *
FROM PLANNED_MISSION PM_CTRL, CONTROL_AGENCY CA,
MISSION_CONTROL MC, MISSION_OBJECTIVE MOR,
REQUEST_OBJECTIVE RO, MISSION_REQUEST_PAIRING MRP,
FRIENDLY_UNIT FU, PACKAGE_MISSION PKM, MISSION_AIRCRAFT MA,
MISSION_OBJECTIVE MO, PLANNED_MISSION PM
WHERE PM.MSN_TASKED_UNIT_TYPE != 'EAM'
AND PM.MSN_INT_ID = MO.MSN_INT_ID
AND PM.MSN_INT_ID = PKM.MSN_INT_ID (+)
AND PM.MSN_INT_ID = MA.MSN_INT_ID (+)
AND COALESCE(MA.MA_RESOURCE_INT_ID,0) =
(SELECT COALESCE(MIN(MA1.MA_RESOURCE_INT_ID),0)
FROM MISSION_AIRCRAFT MA1
WHERE MA.MSN_INT_ID = MA1.MSN_INT_ID)
AND MA.FU_UNIT_ID = FU.FU_UNIT_ID (+)
AND MA.CC_COUNTRY_CD = FU.CC_COUNTRY_CD (+)
AND MO.MSN_INT_ID = MC.MSN_INT_ID (+)
AND MO.MO_INT_ID = MC.MO_INT_ID (+)
AND MC.CAG_CALLSIGN = CA.CAG_CALLSIGN (+)
AND MC.CTRL_MSN_INT_ID = PM_CTRL.MSN_INT_ID (+)
AND MO.MSN_INT_ID = MRP.MSN_INT_ID (+)
AND MO.MO_INT_ID = MRP.MO_INT_ID (+)
AND MRP.REQ_INT_ID = RO.REQ_INT_ID (+)
AND RO.MSN_INT_ID = MOR.MSN_INT_ID (+)
AND RO.MO_INT_ID = MOR.MO_INT_ID (+)
AND MO.MSN_INT_ID = :msn_int_id
AND MO.MO_INT_ID = :obj_int_id
AND COALESCE(PM.MSN_MISSION_NUM, ' ') LIKE '%'
AND COALESCE( PKM.PKG_NM,' ') LIKE '%'
AND COALESCE( MA.FU_UNIT_ID, ' ') LIKE '%'
AND COALESCE( MA.CC_COUNTRY_CD, ' ') LIKE '%'
AND COALESCE(FU.FU_COMPONENT, ' ') LIKE '%'
AND COALESCE( MA.ACT_AC_TYPE,' ') LIKE '%'
AND MO.MO_MSN_CLASS_CD LIKE '%'
AND COALESCE(MO.MO_MSN_TYPE, ' ') LIKE '%'
AND COALESCE( MO.MO_OBJ_LOCATION,COALESCE( MOR.MO_OBJ_LOCATION, ' ')) LIKE '%'
AND COALESCE(CA.CAG_TYPE_OF_CONTROL, ' ') LIKE '%'
AND COALESCE( MC.CAG_CALLSIGN,' ') LIKE '%'
AND COALESCE( MC.ASP_AIRSPACE_NM, ' ') LIKE '%'
AND COALESCE( MC.CTRL_MSN_INT_ID, 0) LIKE '%' AND COALESCE(MC.CTRL_MO_INT_ID, 0) LIKE '%'
AND COALESCE( PM_CTRL.MSN_MISSION_NUM,' ') LIKE '%'
Any help is appreciated.
