Trouble with CTE

Question

Trouble with CTE

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Lynn Pettis (3/13/2011)
Thinking about it, it may need some more tweaking if you think about it. What if a course has two prerequisites each at different levels. Wouldn't you want it listed after the highest prereq of the pair?

Agreed. I was just thinking the same thing as in "Wouldn't that create a cycle?" Heh... Yeah, I know... I need to get a life but this is fun stuff. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Jeff Moden (3/13/2011)
Lynn Pettis (3/13/2011)
Jeff Moden (3/13/2011)
My problem wasn't with making a hierarchical query according to BOL... my problem is that the data the OP provided is cyclic and the examples in BOL don't show how to solve that particular problem. Well done on your part. Now, I have to see what's up with your code to find out how you defeated the cyclic nature of the data. 🙂
Jeff, didn't say that that was your problem. I only stated that I used the example in the book to write the code I came up with to solve the problem, at least partially since the OP tweaked it a bit for the final solution.
Thinking about it, it may need some more tweaking if you think about it. What if a course has two prerequisites each at different levels. Wouldn't you want it listed after the highest prereq of the pair?
Heh... I didn't say you said that was my problem (although I can see how you may have taken it that way). 😉 I was just making a comment in conversation. BWAA-HAA!!! It must be all the Oracle you've had to write lately. 😛

Tell me about it. The more I work with Oracle the more I want to find a new position working with SQL Server again. I have been able to apply my SQL development skills, however, to help eliminate cursors or loops where they aren't needed. Also, I have refactored some code so that it is easier to maintain. We had one stored proc that was 45 pages long after TOAD reformatted the code. When I was done it was only 6 pages long after Toad formatted the code. Yea, Toad formats a little bit weird, but it is readable code.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Boy Howdy! Do I ever have an appreciation for all that. I did a 4 year stint in a mixed environment of T-SQL and PL-SQL. Like you have, I made some great improvements to a lot of existing code and wrote some pretty decent new code but it's not something I ever want to do again. I've actually removed all mention of Oracle from my resume just so folks aren't tempted and neither am I. 😛

Oracle is a darned good RDBMS but it isn't what I cut my teeth on and I had a hell of a time making the necessary and frequent paradigm shifts to be effective in Oracle.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63503 More actions · Answer 4

Lynn,

if a course has multiple prerequisites, take the highest level... I think this works...

/*DECLARE @Sequence int

SET @Sequence = 4;

*/

-- define shape of output

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 final query...

SELECT

CourseName,

CourseNo,

/* reqCourseNo,

nextCourseNo, */

MAX(CourseLevel) AS MaxLevel

FROM

CourseLevels

GROUP BY

CourseName,

CourseNo

ORDER BY MaxLevel, CourseNo;

The MAX(CourseLevel) makes the query return the highest value in the prequisite course levels (IOW, you have to have all the prereqs before taking the course.. (Co-requisites... I think that's more a stored procedure question... At least another post). I added all the courses, and it appears to work properly. At least when I looked at the highest level course, I got the one I know requires the most courses/levels...

Thanks everybody! (And most of the answer was hiding under that F1 key? How do they hide so much stuff under such a small key??? Can that be used during tax season to hide assets?)

From the looks of things, this would be a good query to write as an insert/update query on the Courses table to update/set a "Level"... then if I include the same thing in the Student table, I could quickly filter out what courses they need to take... it's kind of denormalization cheating, but running the CTE against hundreds of records could get ugly. I guess I'll cross that bridge when I get there... after the silly exam.

Thanks!