Binding Error In Sql CASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Binding Error In Sql CASE

Author

Topic

thexman700
Starting Member

3 Posts

Posted - 2011-04-22 : 07:25:15

Greetings everyone,
I am having the usual "The multi-part identifier "ColumnName" could not be bound."

Please refer to the following code
I am having error in the second CASE
where I am using a column name (its value) to compare.
The first case runs fine.....but I want the second case to compare the (FGV)value with column's value

SELECT Distinct Fg.*,

CASE WHEN Fg.FGV >= '40' THEN 'Yes'
ELSE 'No'
END AS [On Track For C Grade],

CASE WHEN Fg.FGV >= dbo.vwOT_Grade.[Challenge Grade Value] THEN 'Yes'
ELSE 'No' END AS [On Track For Challenge Grade]

FROM (SELECT dbo.vwOT_RateOfProgress.PupilKey, dbo.vwOT_RateOfProgress.AssessmentKey, dbo.vwOT_RateOfProgress.SubjectKey, dbo.vwOT_RateOfProgress.[Subject],
dbo.vwOT_RateOfProgress.AltAssessmentKey, dbo.vwOT_RateOfProgress.CertificationType, dbo.vwOT_RateOfProgress.ROP,
dbo.vwOT_Grade.[Actual Grade Value] + dbo.vwOT_RateOfProgress.ROP * (52 - dbo.vwOT_Grade.AcademicWeekNumber) AS FGV
FROM
dbo.vwOT_RateOfProgress INNER JOIN
dbo.vwOT_Grade ON dbo.vwOT_RateOfProgress.PupilKey = dbo.vwOT_Grade.PupilKey AND
dbo.vwOT_RateOfProgress.AssessmentKey = dbo.vwOT_Grade.AssessmentKey AND dbo.vwOT_RateOfProgress.SubjectKey = dbo.vwOT_Grade.SubjectKey AND
dbo.vwOT_RateOfProgress.[Subject] = dbo.vwOT_Grade.[Subject] AND dbo.vwOT_RateOfProgress.AltAssessmentKey = dbo.vwOT_Grade.AltAssessmentKey
) AS Fg

Error:" Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "dbo.vwOT_Grade.Challenge Grade Value" could not be bound."

Please let me know where I am wrong..
thanks

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-22 : 07:42:40

Try this I am not sure this will work or not replay me

;With Cte as
(
SELECT dbo.vwOT_Grade.[Challenge Grade Value],dbo.vwOT_RateOfProgress.PupilKey, dbo.vwOT_RateOfProgress.AssessmentKey, dbo.vwOT_RateOfProgress.SubjectKey, dbo.vwOT_RateOfProgress.[Subject],
dbo.vwOT_RateOfProgress.AltAssessmentKey, dbo.vwOT_RateOfProgress.CertificationType, dbo.vwOT_RateOfProgress.ROP,
dbo.vwOT_Grade.[Actual Grade Value] + dbo.vwOT_RateOfProgress.ROP * (52 - dbo.vwOT_Grade.AcademicWeekNumber) AS FGV
FROM
dbo.vwOT_RateOfProgress INNER JOIN
dbo.vwOT_Grade ON dbo.vwOT_RateOfProgress.PupilKey = dbo.vwOT_Grade.PupilKey AND
dbo.vwOT_RateOfProgress.AssessmentKey = dbo.vwOT_Grade.AssessmentKey AND dbo.vwOT_RateOfProgress.SubjectKey = dbo.vwOT_Grade.SubjectKey AND
dbo.vwOT_RateOfProgress.[Subject] = dbo.vwOT_Grade.[Subject] AND dbo.vwOT_RateOfProgress.AltAssessmentKey = dbo.vwOT_Grade.AltAssessmentKey
)
select *,CASE WHEN FGV >= '40' THEN 'Yes'
ELSE 'No'
END AS [On Track For C Grade],
CASE WHEN FGV >= dbo.vwOT_Grade.[Challenge Grade Value] THEN 'Yes'
ELSE 'No' END AS [On Track For Challenge Grade] from cte

Raghu' S

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-22 : 08:00:32

This dbo.vwOT_Grade.[Challenge Grade Value] isn't an available field to select. You need to either include it as a part of FGV, or add it into your FROM clause.

Jim

Everyday I learn something that somebody else already knew

thexman700
Starting Member

3 Posts

Posted - 2011-04-22 : 08:01:11

@Raghuveer..No luck
same error....:(

thexman700
Starting Member

3 Posts

Posted - 2011-04-22 : 13:07:00

Thanks jimf, I made that column available in the FROM clause

thanks a lot

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-22 : 13:16:17

You're Welcome! Glad it worked.

Jim

Everyday I learn something that somebody else already knew