Re: Outer join with date field?

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

one solution is to create two subqueries slectiong the date portion from test1 and test2. Then make the outer join on those subqueries:

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

>Hi --
>
>I'm using Workgroup Server 7.3
>
>I'm fairly new at this, but I'm trying to join an outer join on date
>fields from two different tables. This works fine if I'm comparing the
>entire date, as in:
>
>Select a.date, b.date from test1 a, test2 b
>where a.date = b.date (+);
>
>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.
>
>Please reply via email, as well as to the group
>
>Thanks in advance,
>Mike
>
>
Received on Thu Feb 05 1998 - 00:00:00 CST