[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-05-04
7
Medium Priority
?
1,299 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:indy500fan
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16605726
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1200 total points
ID: 16605737
The problem was you had 2 status fields and the Order by [Status] will get confused
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 16605738
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:indy500fan
ID: 16605768
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16605779
Split the points
0
 

Author Comment

by:indy500fan
ID: 16605794
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16605821
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question