Having issues with SQL with a join issues I have the following code:

SELECT a.oop_action_id,
  a.requestor,
  a.discussion_topic,
  a.action_item_desc,
  a.owner,
  a.partner,
  a.CP_Director,
  a.Chief_of_Staff,
  a.Date_Due,
  c.empl_email secondary,
  b.empl_email prime,
  d.empl_email cpdirector,
  e.empl_email cos,
  a.mail_sent,
  a.status
FROM OOP_ACTION_ITEMS A, EMPLOYEE B,  EMPLOYEE C,  EMPLOYEE D,  EMPLOYEE E
WHERE a.owner = b.LAST_NAME||','||b.FIRST_NAME||' '||SUBSTR(b.MIDDLE_NAME,1,1)(+)
AND a.partner = c.LAST_NAME||','||c.FIRST_NAME||' '||SUBSTR(c.MIDDLE_NAME,1,1)(+)
AND a.CP_Director = d.first_name||' '||SUBSTR (d.MIDDLE_NAME, 1, 1)||' '||d.last_name(+)
AND a.Chief_of_Staff = e.first_name||' '||SUBSTR (e.MIDDLE_NAME, 1, 1)||' '||e.last_name(+);


I'm trying to run this and I keep getting a 'missing keyword' error on line 17

can anyone explain?
It's one of the many limitations of the old syntax for outer joins. On a join condition of the type:

WHERE  a . a_expression  =  b . b_expression (+) 


while a_expression can be an arbitrary expression involving more than one columns of table a, b_expression can only be a column of table b. For more details, see the related documentations Joins, where it states:


  The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.


To solve this, you can rewrite with the ANSI join syntax:

FROM  OOP_ACTION_ITEMS A
  LEFT JOIN  EMPLOYEE B  
    ON  a.owner 
      = b.LAST_NAME||','||b.FIRST_NAME||' '||SUBSTR(b.MIDDLE_NAME,1,1)
  LEFT JOIN  EMPLOYEE C  
    ON  a.partner
      = c.LAST_NAME||','||c.FIRST_NAME||' '||SUBSTR(c.MIDDLE_NAME,1,1) 
  LEFT JOIN  EMPLOYEE D  
    ON  a.CP_Director 
      = d.first_name||' '||SUBSTR (d.MIDDLE_NAME, 1, 1)||' '||d.last_name
  LEFT JOIN  EMPLOYEE E  
    ON  a.Chief_of_Staff 
      = e.first_name||' '||SUBSTR (e.MIDDLE_NAME, 1, 1)||' '||e.last_name ;