2

I have two tables: T1 and T2

  • T1 has a DATE column: CT1
  • T2 has a DATE column: CT2

I want to left outer join T1 and T2 with join condition:

trunc(CT1,'Mi')=trunc(CT2,'Mi')(+)

When I try to run this SQL, I receive error ORA 00936:missing expression.

Any idea what is wrong here?

Marcus Leon
  • 55,199
  • 118
  • 297
  • 429
ag112
  • 5,537
  • 2
  • 23
  • 42
  • basically my question is can (+) operator can be applied to an arbitrary expression...if yes, how? – ag112 Jun 25 '11 at 04:32

2 Answers2

5

I think that you need to put the (+) operator immediately after the column name that it applies to.

trunc(CT1,'Mi')=trunc(CT2 (+),'Mi')

"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." (from http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm)

In any case, I would suggest using the ANSI syntax. It's clearer, more functional, and portable.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • +1 for the how-to. I agree that burying the (+) operator inside a function reference is not highly readable ;-) – DCookie Jun 25 '11 at 18:49
2

Try using ANSI syntax:

T1 LEFT OUTER JOIN T2 ON TRUNC(CT1,'Mi')=TRUNC(CT2,'Mi')

The (+) outer join syntax has some limitations, this could be one of them. Of course, if you change this join, you'll have to change them all - you can't mix the two.

DCookie
  • 42,630
  • 11
  • 83
  • 92