Fixing the error:"Msg 209, Level 16, State 1, Procedure OperatorQualificationOrderSP, Line 4 Ambiguous column name 'Status'."

Friends,

I have the following SQL syntax, but when it goes to create the Stored Proc, it get the error, "Msg 209, Level 16, State 1, Procedure OperatorQualificationOrderSP, Line 4 Ambiguous column name 'Status'."  This used to work in SQL 2000, but now we are using SQL 2005.  What's causing the error?  Is STATUS now a reserved word?  I wouldn't think this would be a problem since I put brackets around status.

Regards,
Eric

CREATE PROCEDURE dbo.OperatorQualificationOrderSP
            AS
         
            SELECT
           
            QualID,
            [Car Number] = Rtrim([No]),
            Driver = Rtrim(LastName) + ', ' + Rtrim(FirstName),
            Equipment,
            QualifyingTime,
            QualifyingSpeed,
            OnTheBubble =
            CASE
            WHEN OnTheBubble = 0
            THEN 'No'
            WHEN OnTheBubble = 1
            THEN 'Yes'
            WHEN OnTheBubble = 2
            THEN 'No'
            END,
            [Status] =
            CASE
            WHEN [Status] = 100
            Then 'Qualifying'
            WHEN [Status] = 101
            Then 'Pole Day'
            WHEN [Status] = 102
            Then '2nd Day'
            WHEN [Status] = 103
            Then '3rd Day'
            WHEN [Status] = 104
            Then 'Bump Day'
            WHEN [Status] = 105
            Then 'Bumped'
            WHEN [Status] = 106
            Then 'Failed - 1'
            WHEN [Status] = 107
            Then 'Failed - 2'
            WHEN [Status] = 108
            Then 'Failed - 3'
            WHEN [Status] = 109
            Then 'Failed - 4'
            WHEN [Status] = 110
            Then 'Withdrawn'
            WHEN [Status] = 111
            Then 'Waived Off'
            WHEN [Status] = 112
            Then 'No Attempt'
            ELSE 'Unknown'
            END,
            [Status]
       
            FROM
         
            Qualifications
       
            Order by [Status], QualifyingSpeed Desc
indy500fanAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
The problem was you had 2 status fields and the Order by [Status] will get confused
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE PROCEDURE dbo.OperatorQualificationOrderSP
            AS
         
            SELECT
           
            QualID,
            [Car Number] = Rtrim([No]),
            Driver = Rtrim(LastName) + ', ' + Rtrim(FirstName),
            Equipment,
            QualifyingTime,
            QualifyingSpeed,
            OnTheBubble =
            CASE
            WHEN OnTheBubble = 0
            THEN 'No'
            WHEN OnTheBubble = 1
            THEN 'Yes'
            WHEN OnTheBubble = 2
            THEN 'No'
            END,
            [Status1] =      ----------------
            CASE
            WHEN [Status] = 100
            Then 'Qualifying'
            WHEN [Status] = 101
            Then 'Pole Day'
            WHEN [Status] = 102
            Then '2nd Day'
            WHEN [Status] = 103
            Then '3rd Day'
            WHEN [Status] = 104
            Then 'Bump Day'
            WHEN [Status] = 105
            Then 'Bumped'
            WHEN [Status] = 106
            Then 'Failed - 1'
            WHEN [Status] = 107
            Then 'Failed - 2'
            WHEN [Status] = 108
            Then 'Failed - 3'
            WHEN [Status] = 109
            Then 'Failed - 4'
            WHEN [Status] = 110
            Then 'Withdrawn'
            WHEN [Status] = 111
            Then 'Waived Off'
            WHEN [Status] = 112
            Then 'No Attempt'
            ELSE 'Unknown'
            END,
            [Status]  ------------
       
            FROM
         
            Qualifications
       
            Order by [Status], QualifyingSpeed Desc
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you have 2 columns called status as result of the query, this cannot have worked in sql 2000 neither:

CREATE PROCEDURE dbo.OperatorQualificationOrderSP
            AS
         
            SELECT
           
            QualID,
            [Car Number] = Rtrim([No]),
            Driver = Rtrim(LastName) + ', ' + Rtrim(FirstName),
            Equipment,
            QualifyingTime,
            QualifyingSpeed,
            OnTheBubble =
            CASE
            WHEN OnTheBubble = 0
            THEN 'No'
            WHEN OnTheBubble = 1
            THEN 'Yes'
            WHEN OnTheBubble = 2
            THEN 'No'
            END,
            [Status Text] =
            CASE
            WHEN [Status] = 100
            Then 'Qualifying'
            WHEN [Status] = 101
            Then 'Pole Day'
            WHEN [Status] = 102
            Then '2nd Day'
            WHEN [Status] = 103
            Then '3rd Day'
            WHEN [Status] = 104
            Then 'Bump Day'
            WHEN [Status] = 105
            Then 'Bumped'
            WHEN [Status] = 106
            Then 'Failed - 1'
            WHEN [Status] = 107
            Then 'Failed - 2'
            WHEN [Status] = 108
            Then 'Failed - 3'
            WHEN [Status] = 109
            Then 'Failed - 4'
            WHEN [Status] = 110
            Then 'Withdrawn'
            WHEN [Status] = 111
            Then 'Waived Off'
            WHEN [Status] = 112
            Then 'No Attempt'
            ELSE 'Unknown'
            END,
            [Status]
       
            FROM
         
            Qualifications
       
            Order by [Status], QualifyingSpeed Desc
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
indy500fanAuthor Commented:
I'll buy your explaination for a dollar, but I swear it worked in SQL 2000.  

aneeshattingal and angelIII you both answered within a couple of seconds of each other with the same response.  Do you both think I should split the points.  Technically, aneeshattingal was first.

Thanks,
Eric
 
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Split the points
0
 
indy500fanAuthor Commented:
aneeshattingal,

That is very kind of you.  I am going to give you an edge in the split since you were technically first.  Sound good?

Thanks to both of you!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I usually defend the same idea of rather splitting the points when 2 (or more) comments come within the short time frame.
Anyhow, glad we could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.