|
|
Posted by ll on 01/04/08 20:53
On Dec 24 2007, 2:48 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> ll wrote:
> > SELECT AMS_CourseCategory.CourseCatDesc,
> > AMS_CourseCategory.CourseCatID, AMS_Courses.Class,
> > AMS_Courses.Semester, AMS_Courses.TemplateVersion,
> > AMS_Courses.YearInProgram, AMS_Courses.CourseID,
> > AMS_Courses.Class,AMS_Courses.Semester, AMS_Courses.YearTaught,
> > AMS_ContentOverviewObjectiveOutcome.CourseID,
> > AMS_ContentOverviewObjectiveOutcome.Objective,
> > AMS_ContentOverviewObjectiveOutcome.Out1,
> > AMS_ContentOverviewObjectiveOutcome.Out9,
> > AMS_ContentOverviewObjectiveOutcome.Out8,
> > AMS_ContentOverviewObjectiveOutcome.OutP15,
> > AMS_ContentOverview.Complete FROM AMS_ContentOverviewObjectiveOutcome
> > INNER JOIN AMS_Courses ON AMS_ContentOverviewObjectiveOutcome.CourseID
> > = AMS_Courses.CourseID INNER JOIN AMS_CourseCategory ON
> > AMS_Courses.CourseCatID = AMS_CourseCategory.CourseCatID INNER JOIN
> > AMS_ContentOverview ON AMS_Courses.CourseID =
> > AMS_ContentOverview.CourseID WHERE Semester = 'FA-SP' OR Semester =
> > 'FA' OR Semester = 'SP' AND Out8 = 'N' order by AMS_Courses.CourseCatID
>
> The AND/OR issue has already been answered by others. I just feel
> obliged to point out that you've got a ton of redundant verbiage due
> to non-use of prefixes. Compare this rewrite:
>
> select cc.CourseCatDesc,
> cc.CourseCatID,
> c.Class,
> c.Semester,
> c.TemplateVersion,
> c.YearInProgram,
> c.CourseID,
> c.Class,
> c.Semester,
> c.YearTaught,
> cooo.CourseID,
> cooo.Objective,
> cooo.Out1,
> cooo.Out9,
> cooo.Out8,
> cooo.OutP15,
> co.Complete
> from AMS_ContentOverviewObjectiveOutcome cooo
> join AMS_Courses c on cooo.CourseID = c.CourseID
> join AMS_CourseCategory cc on c.CourseCatID = cc.CourseCatID
> join AMS_ContentOverview co on c.CourseID = co.CourseID
> where c.Semester in ('FA-SP', 'FA', 'SP')
> and cooo.Out8 = 'N'
> order by c.CourseCatID
>
> Some stylistic notes:
>
> * c.Semester is redundant (appears twice)
>
> * cooo.CourseID is redundant (always same value as c.CourseID)
>
> * ORDER BY uses c.CourseCatID while SELECT uses cc.CourseCatID
> (though the values are always the same)
>
> * Out1, Out9, and Out8 look like a 1NF violation. I don't know
> what the deal is with OutP15.
>
> http://en.wikipedia.org/wiki/First_normal_form#Example_2:_Repeating_g...
>
> What does the entire AMS_ContentOverviewObjectiveOutcome table
> look like? Consider refactoring it, or at least maintaining and
> using a view that presents its data in a 1NF-compliant form (then
> revising the code gradually over time, until all references to the
> non-1NF form are eventually eliminated).
Thanks for all the input! Yes, the grouping was the answer. In
revising the code, I'm sure I'll consider the prefixes at some point
in the stage.
Navigation:
[Reply to this message]
|