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 (+)

OMG Ponies · Accepted Answer · 2010-01-18 20:04:39Z

2

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

answered Jan 18, 2010 at 20:04

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ChristopheD · Accepted Answer · 2010-01-18 20:05:08Z

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.

answered Jan 18, 2010 at 20:05

ChristopheD

117k30 gold badges167 silver badges182 bronze badges

Collectives™ on Stack Overflow

Oracle Syntax to Postgres Help

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related