Oracle Syntax to Postgres Help

Question

I am trying to convert this code from Oracle:

CREATE VIEW PLANNED AS
SELECT ASP.ASP_SPACE_NM,
       SUM(MO.MO_TKR_TOTAL_OFF_SCHEDULED) "TOTAL_PLANNED"
  FROM OBJECTIVE MO, SPACE ASP
 WHERE ASP.ASP_SPACE_NM = MO.ASP_SPACE_NM (+)
   AND MO.MO_MSN_CLASS_NM = 'EPIC'
 GROUP BY ASP.ASP_SPACE_NM

To Postgres.

Two lines in this are confusing me:

   SUM(MO.MO_TKR_TOTAL_OFF_SCHEDULED) "TOTAL_PLANNED"

Is this the same as this?

   SUM(MO.MO_TKR_TOTAL_OFF_SCHEDULED) AS TOTAL_PLANNED

And I have no idea what the (+) means in this line:

 WHERE ASP.ASP_SPACE_NM = MO.ASP_SPACE_NM (+)

Answer 1

That's old school, Oracle specific LEFT JOIN syntax. You want to replace it with:

CREATE VIEW PLANNED AS
   SELECT s.asp_space_nm,
          SUM(o.mo_tkr_total_off_scheduled) 'total_planned'
     FROM SPACE s
LEFT JOIN OBJECTIVE o ON o.asp_space_nm = s.asp_space_nm 
                     AND o.mo_msn_class_nm = 'EPIC'
 GROUP BY s.asp_space_nm

Reference:

• Oracle LEFT JOIN

Answer 2

The + indicates an (edit: left) outer join.

And

SUM(MO.MO_TKR_TOTAL_OFF_SCHEDULED) "TOTAL_PLANNED"

is indeed very probably identical to

SUM(MO.MO_TKR_TOTAL_OFF_SCHEDULED) as "TOTAL_PLANNED"

If I recall correctly as is not really needed in PostgreSQL as well (although it makes it a lot more explicit in my view).

You could have a look at the following page on the Postgresql wiki for some more differences: Oracle to Postgres conversion.