[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1284
  • Last Modified:

Oracle 8.i outer join with trunc clause not working

Hello.  I have a view that creates calendar days:
select rnum, to_date('1-JAN-2009') +rnum CreatedDiaryDate
      from ( select rownum-1 rnum
               from all_objects
              where rownum <= 2000)

I need to see all of the calendar days from this view, and matches from a table called diary if there are any.  So, I have an outer join on the date fields.  I need to trunc the joined fields, because I need to discount the time component.  Here is the query:

SELECT rnum, createddiarydate, ddiarydate, dsubjectid
  FROM v_createdate , diary
where trunc(createddiarydate) = TRUNC(ddiarydate(+))

However, this query only ever returns exact matches on the dates.  I need to see all dates from the v_createdate view, so that I can show what days from the diary table  are missing.
Thank you.
0
KMBrochu
Asked:
KMBrochu
  • 5
  • 4
  • 2
  • +1
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
where trunc(createddiarydate) = TRUNC(ddiarydate)(+)
0
 
KMBrochuAuthor Commented:
I get a 4:12:53 PM  ORA-00933: SQL command not properly ended
error when I move the (+)


SELECT rnum, createddiarydate, ddiarydate, dsubjectid
  FROM v_createdate , diary
where trunc(createddiarydate) = TRUNC(ddiarydate)(+)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please clarify which field comes from which table?
using table aliases will be a key factor there :)

also, you might want to use the LEFT JOIN syntax ...
something like this, where I had to guess which table the columns are in:
SELECT d.rnum, d.createddiarydate, v.ddiarydate, d.dsubjectid
  FROM v_createdate v
  LEFT JOIN diary d
    ON trunc(d.createddiarydate) = TRUNC(v.ddiarydate)

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
I'm probably missing something but using a small test case, what you had appears to work for me.
drop table tab1 purge;
create table tab1(col1 date);


drop table tab2 purge;
create table tab2(col1 date);

insert into tab1 values(to_date('01/01/2001 01:01:01','MM/DD/YYYY HH24:MI:SS'));
insert into tab1 values(to_date('02/02/2002 01:01:01','MM/DD/YYYY HH24:MI:SS'));
insert into tab1 values(to_date('03/03/2003 03:03:03','MM/DD/YYYY HH24:MI:SS'));

insert into tab2 values(to_date('01/01/2001 01:01:01','MM/DD/YYYY HH24:MI:SS'));
insert into tab2 values(to_date('02/02/2002 02:02:02','MM/DD/YYYY HH24:MI:SS'));
commit;

select t1.col1, t2.col1
from tab1 t1, tab2 t2
where trunc(t1.col1) = trunc(t2.col1(+))
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>LEFT JOIN syntax
Was this around back in 8i?  I can't remember when Oracle decided to follow the ANSI standard.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
slightwv: excellent remark.
the left join only started with oracle 9i: http://www.dba-oracle.com/tips_oracle_left_outer_join.htm
0
 
KMBrochuAuthor Commented:
Folks- I cannot use the LEFT JOIN syntax in 8i.

AngelIII: createddiarydate comes from the view called v_createdate, and diarydate comes from the table called diary.

Is it having problems because the view is creating the dates on the fly?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is it having problems because the view is creating the dates on the fly?
can you clarify that, please?
0
 
KMBrochuAuthor Commented:
The view creates calendar days for me:

select rnum, to_date('1-JAN-2009') +rnum CreatedDiaryDate
      from ( select rownum-1 rnum
               from all_objects
              where rownum <= 2000)

The view is not based on an underlying table structure...I was wondering if that would cause this issue.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, the date value in that view IS already truncated :)

so, should it not be like this?
where createddiarydate = (TRUNC(ddiarydate))(+)

Open in new window

0
 
sdstuberCommented:
alternatively try this...

if you have an index on ddiarydate using TRUNC will prevent use of it unless you create a function based index


SELECT rnum, createddiarydate, ddiarydate, dsubjectid
  FROM v_createdate , diary
where  ddiarydate(+) >= createddiarydate
and ddiarydate(+) < createddiarydate + 1


also, I recommend using an explicit mask on your TO_DATE in the v_createdate view.  
0
 
KMBrochuAuthor Commented:
I did not know that about the trunc with the indexed field.  I will keep that in mind for later use.  
I  was able to get my query to work by tweeking it this way:

select a.calendardate, b.ddiarydate from
(select * from calendartest)a,
(select * from diary where dsubjectid = 11)b
where trunc(a.calendardate) = trunc(b.ddiarydate(+))

Thank you experts!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now