March 20, 2011 at 10:22 pm
With a lot of help from Lynn and Jeff, I created a CTE that calculates levels in a hierarchy of courses. See this post:
http://www.sqlservercentral.com/Forums/FindPost1077503.aspx
What I'm trying to do now is to update the Course table with the Level that is calculated in the CTE, but I'm failing miserably. Here's my latest attempt, but it doesn't like my GROUP BY
-- define shape of output
use Pig;
go
with CoursePrereqs (
CourseName,
CourseNo,
reqCourseNo,
nextCourseNo
)as (
-- show all courses with all their prereqs.
-- LEFT join because some courses have no prereqs
select
c.CourseName,
c.CourseNo,
p.reqCourseNo,
p.nextCourseNo
from
dbo.Course c
left outer join dbo.Prereq p
on (c.CourseNo = p.nextCourseNo)
),
-- This is a second CTE
CourseLevels (
CourseName,
CourseNo,
reqCourseNo,
nextCourseNo,
CourseLevel
) as (
select
CourseName,
CourseNo,
reqCourseNo,
nextCourseNo,
1 as CourseLevel
from
CoursePrereqs -- first CTE
where
nextCourseNo is null
union all
select
cp.CourseName,
cp.CourseNo,
cp.reqCourseNo,
cp.nextCourseNo,
CourseLevel + 1
from
CoursePrereqs cp
inner join CourseLevels cl
on (cl.CourseNo = cp.reqCourseNo)
),
-- this is the third CTE ...
MaximumLevels (
CourseName,
CourseNo,
CourseLevel
) as (
SELECT TOP 100 PERCENT
CourseName,
CourseNo,
MAX(CourseLevel) AS MaxLevel
FROM
CourseLevels
GROUP BY
CourseName,
CourseNo
ORDER BY MaxLevel, CourseNo)
-- this is the final CTE
UPDATE Course
SET [Level]= MaximumLevels.CourseLevel
WHERE CourseNo = MaximumLevels.CourseNo;
the SELECT stuff works a charm... I was just thinking that since the course hierarchy was fairly stable (well, unless course requirements are updated), I could/should set the "Level" value in the Course table. (So I can cheat and set new students to be "Level 1" and then increment as they finish courses... that way I can just cheat and join them to their major and level to find their next courses).
If anybody has really good references/websites for a CTE noob like me, that would be great!
Thanks!
Pieter
March 20, 2011 at 11:05 pm
The problem isn't your GROUP BY it is that you haven't referenced the MaximumLevels CTE in your UPDATE query.
Give this a try:
with CoursePrereqs (
CourseName,
CourseNo,
reqCourseNo,
nextCourseNo
)as (
-- show all courses with all their prereqs.
-- LEFT join because some courses have no prereqs
select
c.CourseName,
c.CourseNo,
p.reqCourseNo,
p.nextCourseNo
from
dbo.Course c
left outer join dbo.Prereq p
on (c.CourseNo = p.nextCourseNo)
),
-- This is a second CTE
CourseLevels (
CourseName,
CourseNo,
reqCourseNo,
nextCourseNo,
CourseLevel
) as (
select
CourseName,
CourseNo,
reqCourseNo,
nextCourseNo,
1 as CourseLevel
from
CoursePrereqs -- first CTE
where
nextCourseNo is null
union all
select
cp.CourseName,
cp.CourseNo,
cp.reqCourseNo,
cp.nextCourseNo,
CourseLevel + 1
from
CoursePrereqs cp
inner join CourseLevels cl
on (cl.CourseNo = cp.reqCourseNo)
),
-- this is the third CTE ...
MaximumLevels (
CourseName,
CourseNo,
CourseLevel
) as (
SELECT
CourseName,
CourseNo,
MAX(CourseLevel) AS MaxLevel
FROM
CourseLevels
GROUP BY
CourseName,
CourseNo)
-- this is the final CTE
UPDATE C
SET [Level]= MaximumLevels.CourseLevel
FROM Course C
INNER JOIN MaximumLevels
ON C.CourseNo = MaximumLevels.CourseNo;
BTW: You will notice I took the "TOP 100 PERCENT" and the "ORDER BY" out of the CTE since they weren't necessary and didn't help anything.
March 20, 2011 at 11:56 pm
Great! Thanks! I knew it had to be something stupid I was overlooking.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply