Re: Just as a learning exercise

From: <TESTAJ3_at_nationwide.com>
Date: Wed, 4 May 2011 09:31:21 -0400
Message-ID: <OF3F72673C.24C0FF67-ON85257886.004A3F52-85257886.004A487E_at_lnotes-gw.ent.nwie.net>

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_at_gmail.com>
To:
tomdaytwo_at_gmail.com
Cc:
oracle-l <oracle-l_at_freelists.org>
Date:
05/04/2011 09:23 AM
Subject:
Re: Just as a learning exercise
Sent by:
oracle-l-bounce_at_freelists.org

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_at_gmail.com> 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.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 04 2011 - 08:31:21 CDT