asked on

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

Aneesh

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

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

indy500fan

ASKER

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

Aneesh

Split the points

indy500fan

ASKER

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!

Guy Hengel [angelIII / a3]

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

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

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