"Invalid Identifier" when joining on a column I created in the SELECT Statement

Question

Note: My research turned up this question, which provides a possible solution to my issue, but my question is more general: is that the kind of solution I should go for?

I would like to query an academic history database to give me a record for each pair of calculus classes a particular student has taken where one is the prerequisite of the other. If the database was set up nicely or the course numbering was reasonable, I could do:

SELECT ...
FROM Academic_History PrerequisiteCourse
JOIN Academic_History NextCourse 
     ON (NextCourse.CalculusLevel = PrerequisiteCourse.CalculusLevel + 1) 
WHERE ...

Of course the CalculusLevel field doesn't exist, so this is nonsense. Also, there are several course numbers that qualify as Calculus I, and several that qualify as Calculus II, and so on, and these change fairly often. That makes hardcoding all the prerequisite pairings into the JOIN statement like this a really bad idea:

SELECT ...
FROM Academic_History PrerequisiteCourse
JOIN Academic_History NextCourse 
     ON (NextCourse.CourseNumber = '231' AND PrerequisiteCourse.CourseNumber = '220'
      OR NextCourse.CourseNumber = '231' AND PrerequisiteCourse.CourseNumber = '221'
      OR NextCourse.CourseNumber = '241' AND PrerequisiteCourse.CourseNumber = '231'
      OR NextCourse.CourseNumber = '24-' AND PrerequisiteCourse.CourseNumber = '231'   
      ...)
WHERE ...

What I feel like I should do is create my "CalculusLevel" field on the fly, which would be much easier to maintain:

SELECT CASE PrerequisiteCourse.CRS_NBR
            WHEN '115' THEN '0'
            WHEN '220' THEN '1'
            WHEN '221' THEN '1' 
            ...
            END PrerequisiteCourseLevel,
       CASE NextCourse.CRS_NBR
            WHEN '115' THEN '0'
            WHEN '220' THEN '1'
            WHEN '221' THEN '1' 
            ...
            END NextCourseLevel,

FROM Academic_History PrerequisiteCourse
JOIN Academic_History NextCourse 
     ON (PrerequisiteCourseLevel + 1 = NextCourseLevel)
WHERE ...

But of course the join doesn't work, since those columns are not in those tables. Even if I move the condition out of the JOIN ON and into the WHERE clause, though, I get an "Invalid Identifier" error, presumably because these fields don't exist yet when the WHERE clause is being executed.

What's the right way to do this? I've come up with a couple solutions like the one I mentioned in the second code block, but they all feel like unprofessional hacks.

Thanks!

Not sure what you're asking here. This seems like a more of a data modelling problem so why have you framed it as a syntax question? — APC, Jun 23 '11 at 20:55
I tried to word the title such that the next version of me would find it via Google. My question is, "should I use my ugly hack in code block #2, or should I use the kind of ugly hack in the linked question, or is there actually some good way to do this?" — Chris Cunningham, Jun 23 '11 at 21:00
Have you considered adding these course levels to the table itself, rather than having to maintain them in an ugly case statement? — Damien_The_Unbeliever, Jun 24 '11 at 07:56
@Damien Unfortunately although one of my favorite tags is database_design, I don't get to change the design since I'm in a large bureaucracy. :) — Chris Cunningham, Jun 24 '11 at 18:19

Andomar · Accepted Answer · 2011-06-24T07:13:12.323

2

You could add reusable columns using a CTE:

;with   hist as
        (
        select  case ... end as NextCourseLevel
        ,       case ... end as PrerequisiteCourseLevel
        ,       *
        from    Academic_History
        )
select  *
from    hist t1
join    hist t2
on      t1.PrerequisiteCourseLevel + 1 = t2.NextCourseLevel

EDIT: Per your comment, you can refactor the with statement by expanding it everywhere it's used:

select  *
from    (
        select  case ... end as PrerequisiteCourseLevel
        ,       *
        from    Academic_History
        ) as t1
join    (
        select  case ... end as NextCourseLevel
        ,       *
        from    Academic_History
        ) as t2
on      t1.PrerequisiteCourseLevel + 1 = t2.NextCourseLevel

edited Jun 24 '11 at 07:13

answered Jun 23 '11 at 20:58

Andomar

232,371
49
380
404

That looks excellent and is probably the right answer for future people who have this issue. Unfortunately I've just discovered that my access to the database is actually limited exclusively to SELECT statements....... I suppose I can mostly replicate what you did with a subquery, which is probably essentially identical? – Chris Cunningham Jun 23 '11 at 21:10
Ah, my vague followup question is nicely addressed [here](http://stackoverflow.com/questions/706972/difference-between-cte-and-subquery). – Chris Cunningham Jun 23 '11 at 21:11
1

Ask for a "create view" grant, and create a view that exposes the derived CalculusLevel data - that way when you need to add another course to it you only need to change the view & not every single query that requires the derived data. – Phil Jun 23 '11 at 21:40
in Oracle the statement delimiter `;` has to be put at the end of the statement not at the beginning – Jun 23 '11 at 22:09

"Invalid Identifier" when joining on a column I created in the SELECT Statement

1 Answers1