extractValue and right outer join - Oracle SQL

Question

i got a little (at least i hope so) problem with an SQL Query. Here's my join

WHERE obj.mod_type = 'SER'
   and obj.wrk_id=wrk_lang.id(+)
   and extractvalue(value(shm),'/*/@xmi:id','xmlns:xmi="http://www.omg.org/XMI"') = extractvalue(value(shm_con),'/*/@source')(+)

It gives me an "ORA-00936: missing expression"

When i remove the second outer join it works fine(also with a regular join).

Can smb. help me?

probably you should write `value(shm_con(+))`. But using ANSI JOIN would be better. — Florin Ghita, Oct 10 '12 at 11:26

score 0 · Accepted Answer · answered Oct 10 '12 at 11:48

It looks to me like this is actually a LEFT outer join - in old-style Oracle syntax the (+) is put on the optional side of the join while in ANSI syntax the required side (LEFT or RIGHT) is called out in the JOIN specification - see this AskTom reference for examples. So, expanding on @FlorinGhita's recommendation to use ANSI JOIN syntax (and making a bit of an assumption about the rest of the statement) we'd get something like

SELECT *
  FROM OBJ
  LEFT OUTER JOIN WRK_LANG
    ON (obj.wrk_id = wrk_lang.id and
        extractvalue(value(obj.shm),'/*/@xmi:id','xmlns:xmi="http://www.omg.org/XMI"') = 
          extractvalue(value(wrk_lang.shm_con),'/*/@source')
  WHERE obj.mod_type = 'SER';

Share and enjoy.

Thx for your help! I solved the Problem with Nested Sets und an ANSI Join. — FredM, Oct 10 '12 at 12:08

extractValue and right outer join - Oracle SQL

1 Answers1