CODEWITHSUNDEEP
sqloracle-databasejoinoracle11gouter-join
Xuân Trọng Nguyễn
Xuân Trọng Nguyễn

Reputation: 11

ORA-00936 when select statement

SELECT * FROM A,B
WHERE 
A.COMP_CODE = B.COMP_CODE (+)
AND 
A.DATE = TO_CHAR(ADD_MONTHS(TO_DATE(B.DATE,'yyyymm'),-1),'YYYYMM') (+)

When I execute this statement I get error: ORA-00936. When I remove the (+) at the end this statement then it works, but I want keep (+) in this statement.

Upvotes: 0

Views: 90

Answers (1)

Mureinik
Mureinik

Reputation: 311338

When using the old (+) syntax for outer joins, the (+) should directly follow the column your're outer-joining on, not the entire expression:

SELECT *
FROM   A, B
WHERE  A.COMP_CODE = B.COMP_CODE (+)
       AND 
       A.DATE = TO_CHAR(ADD_MONTHS(TO_DATE(B.DATE (+), 'yyyymm'), 'YYYYMM')
       -- Here -----------------------------------^

But honestly, this syntax has been considered deprecated for several years no. You'd probably be better off using the modern explicit outer join syntax: 

SELECT *
FROM   A
LEFT OUTER JOIN B ON -- Here!
    A.COMP_CODE = B.COMP_CODE
    AND 
    A.DATE = TO_CHAR(ADD_MONTHS(TO_DATE(B.DATE, 'yyyymm'), 'YYYYMM')

Upvotes: 2

Related Questions