Re: Just as a learning exercise
- From: Paul Drake <bdbafh@xxxxxxxxx>
- To: tomdaytwo@xxxxxxxxx
- Date: Wed, 4 May 2011 09:19:56 -0400
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:
