Re: Outer join with date field?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/02/05
Message-ID: <34d97089.1117412@www.sigov.si>#1/1

On Wed, 4 Feb 1998 22:01:12 GMT, Mike Schwartz <mls517_at_geocities.com> wrote:

> ...[SNIP]....
>However, this is not what I need. I need to be able to comparing only
>the date portion of the fields, not the time. I'd like to be able to do
>something like this:
>
>Select a.date1, b.date2 from test1 a, test2 b
>where to_char(a.date,'YYDDD') = to_char(b.date,'YYDDD') (+);
>
>This doesn't work, yielding an ORA-00933 error: SQL command not
>properly ended.
>
>Is there any way to do this? It works great as an inner join, but being
>able to do outer joins in this manner would be very useful. I suppose I
>could split up the date and time data into 2 columns, but I'd really
>rather not.

Don't put outer join operator (+) after the expression, put it after the column *inside* the expression and it'll work, i.e:

Select a.date1, b.date2 from test1 a, test2 b where to_char(a.date,'YYDDD') = to_char(b.date(+),'YYDDD');

                                              ***

Regards,

>Please reply via email, as well as to the group
>
>Thanks in advance,
>Mike
>
>

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Feb 05 1998 - 00:00:00 CST