Re: Just as a learning exercise
- From: TESTAJ3@xxxxxxxxxxxxxx
- To: bdbafh@xxxxxxxxx
- Date: Wed, 4 May 2011 09:31:21 -0400
to truncate the time component?
Joe
_______________________________________
Joe Testa, Oracle Certified Professional
Senior Engineering & Administration Lead
(Work) 614-677-1668
(Cell) 614-312-6715
From:
Paul Drake <bdbafh@xxxxxxxxx>
To:
tomdaytwo@xxxxxxxxx
Cc:
oracle-l <oracle-l@xxxxxxxxxxxxx>
Date:
05/04/2011 09:23 AM
Subject:
Re: Just as a learning exercise
Sent by:
oracle-l-bounce@xxxxxxxxxxxxx
Thomas,
Why would anyone ever apply a TRUNC ( ) function to a date column in a
where clause?
Is there no possibility that one might want to index that column at some
point?
Are you that fond of having to create function based indexes just because
devs can't write decent SQL statements?
Paul
On Wed, May 4, 2011 at 9:10 AM, Thomas Day <tomdaytwo@xxxxxxxxx> wrote:
I've got a query in ANSI join format that returns all employees along with
the required training courses and the ethics training. If they've
completed a course then there will be a completed date in the
training_survey table. If not, then the completed date will be blank,
indicating that that employee needs to take that course. I have an ANSI
join query that returns one row per employee per course, whether they've
completed the course during the fiscal year or not.
The ANSI join looks like:
select p.last_name, p.first_name,s.title, t.created as completed
from persons p
join required_courses s on 1=1
left join training_survey t on s.ap_code = t.survey_app and t.user_id =
p.id
and trunc(t.created) >= to_date('10/01/2009', 'MM/DD/YYYY')
and trunc(t.created) <= to_date('09/30/2010', 'MM/DD/YYYY')
where s.type = 'survey'
union
select p.last_name, p.first_name, 'Ethics' AS title, created as completed
from persons p
left join ethics_training u on p.id = u.userid
and created between to_date('10/01/2009', 'MM/DD/YYYY')
and to_date('09/30/2010', 'MM/DD/YYYY')
and checkthis = 1
where 1=1
order by last_name, first_name;
My question is, what would with look like with the Oracle join (+)? I've
tried several variations but it doesn't return the rows were there is no
completed date within the fiscal year.
I want the Cartesian product of employees and courses. I can do that.
The problem is in translating
left join training_survey t on s.ap_code = t.survey_app and t.user_id =
p.id
and trunc(t.created) >= to_date('10/01/2009', 'MM/DD/YYYY')
and trunc(t.created) <= to_date('09/30/2010', 'MM/DD/YYYY')
So that it pulls in a created date when one is available but, when one is
not available, it still leaves the row in the result set.
It's not a problem; I'd just like to learn how to do that.
Other related posts:
