Results 1 to 4 of 4

Thread: Error Code 207

  1. #1
    Join Date
    Dec 2007
    Posts
    5

    Unanswered: Error Code 207

    I am getting the 207 error code and can't figure out the problem. I have a coldfusion page that is calling a stored procedure. The page allows users to sort the displayed columns and it is passing the column numbers into my stored procedure. I am using a case statement to decide which sort to use.

    The error message is: Invalid column name 'SCHOOL'.
    I've tried adding single and double quotes here and it worked before I added the case statment when I just type 'order by school'.

    Below is some of my code:

    Code:
    SELECT  P.NAME,
                P.ASORG,
                P.MAILSTOP,
                P.OFCPHONE,
                R.EMPLID,
                SCHOOL = S.DESCR,
                R.DEGREE_LEVEL,
                R.DISCIPLINE,
                TEAM = T.TEAM_NAME,
                NAT_EVENT = NE.EVENT_NAME,
                R.SCHOOL_CODE,
                R.TEAM_CODE,
                R.EVENT_CODE,
    FROM    HR_EXTRAS..RECRUITERS R,
               HRWH..PS_SCHOOL_TBL S,
               HRWH..PERSON P,
               HR_Extras..UP_TEAMS T,
               HR_Extras..UP_NATIONAL_EVENTS NE,
    WHERE   R.SCHOOL_CODE    =  S.SCHOOL_CODE
    AND     R.EMPLID = P.SNL_ID
    AND     T.TEAM_CODE = R.TEAM_CODE
    AND     NE.EVENT_CODE = R.EVENT_CODE
    ORDER BY CASE	WHEN @SORT = 1 THEN P.NAME
    				WHEN @SORT = 2 THEN P.ASORG
    				WHEN @SORT = 3 THEN SCHOOL
    				WHEN @SORT = 4 THEN T.TEAM_NAME
    				WHEN @SORT = 5 THEN NE.EVENT_NAME
    				END
    Can anyone offer suggestions?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    WHEN @SORT = 3 THEN S.DESCR
    -PatP

  3. #3
    Join Date
    Dec 2007
    Posts
    5
    Code:
    WHEN @SORT = 3 THEN S.DESCR
    That doesn't work because the SQL statement is actually part of a union query. The previous statements pull the exact same data but have

    Code:
    SCHOOL = ' NM-Not Assigned',
    and

    Code:
    SCHOOL = ' CA-Not Assigned',
    Sorry I didn't include that previously. The two values above are not stored in the PS_SCHOOL_TBL, thus the explicit call.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    deleted comment
    Last edited by corncrowe; 12-06-07 at 13:43.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •