Porting join from Oracle to Postgres

Question

INSERT INTO MISSION_OBJECTIVE( MSN_INT_ID, MO_INT_ID, MO_MSN_CLASS_NM,
MO_MSN_CLASS_CD, MO_MSN_TYPE, MO_PRIORITY, MO_COMMENT, MO_START_DT,
MO_END_DT, ASP_AIRSPACE_NM, MO_OBJ_LOCATION, MO_ALO_LEG_ID,
MO_ALO_ARRIVE_LOC) SELECT '1025', '1', 'AIRDROP', 'ADP', 'LAPES', NULL,
COALESCE( NULL, ' '), TO_TIMESTAMP( '1002260900', 'YYMMDDHH24MI'),
TO_TIMESTAMP( '1002260915', 'YYMMDDHH24MI'), 'TRANSIT ALPHA', 'TRANSIT ALPHA', '1', 'TRANSIT ALPHA'
FROM AIRSPACE ASP, apsmain .MISSION_CLASS MC WHERE ASP.ASP_AIRSPACE_NM(+)= 'TRANSIT ALPHA'
AND MC.MCS_MISSION_CLASS_NAME= 'AIRDROP' AND 'TRANSIT ALPHA'
IS NOT NULL

The part that is confusing me is that ASP.ASP_AIRSPACE_NM is being right joined to a constant.

How can I port this to use a normal right join?

Answer 1

It isn't quite the same - the (+) specifies an outer join in the land of Oracle.

The equality of the statements is really going to be tied to the data in your AIRSPACE table, though honestly reading the statement I don't think the (+) modifier actually does anything useful in this case other than probably throw off the optimizer.

Answer 2

Tidied the formatting up a bit.

INSERT
INTO MISSION_OBJECTIVE
  (
    MSN_INT_ID, MO_INT_ID, MO_MSN_CLASS_NM, MO_MSN_CLASS_CD,
    MO_MSN_TYPE, MO_PRIORITY, MO_COMMENT, MO_START_DT,
    MO_END_DT, ASP_AIRSPACE_NM, MO_OBJ_LOCATION, MO_ALO_LEG_ID,
    MO_ALO_ARRIVE_LOC
  )
SELECT '1025', '1', 'AIRDROP', 'ADP',
  'LAPES', NULL, COALESCE( NULL, ' '), TO_TIMESTAMP( '1002260900', 'YYMMDDHH24MI'),
  TO_TIMESTAMP( '1002260915', 'YYMMDDHH24MI'), 'TRANSIT ALPHA', 'TRANSIT ALPHA', '1',
  'TRANSIT ALPHA'
FROM AIRSPACE ASP, apsmain .MISSION_CLASS MC
WHERE ASP.ASP_AIRSPACE_NM(+) = 'TRANSIT ALPHA'
AND MC.MCS_MISSION_CLASS_NAME= 'AIRDROP'

Observations:

• The SELECT list only consists of constants.

• There is no join condition between AIRSPACE and APSMAIN.MISSION_CLASS

• The MISSION_CLASS is filtered by the 'AIRDROP' predicate. If there are no AIRDROP rows, then no rows will be inserted.

• The AIRSPACE table is apparently filtered by the TRANSIT ALPHA predicate. However with the Outer Join, it will always return at least one row, but may return multiple rows.

It will insert one row for each AIRDROP row if there are zero or one TRANSIT ALPHA rows If there are multiple TRANSIT ALPHA rows, the number of rows inserted will be multipleid. For example, three AIRDROP rows and two TRANSIT ALPHA rows will result in six rows being inserted.