IIF and SQL Server 2000

[ssailer]

I'm ready to pull my hair out. I have just created a new view in SQL Server 2000 using the query designer. However, I cannot get the code to accept a case statement, which is supposed to replace the IIF statement that I was using in Access. Here is the code in the query designer:

SELECT dbo.company.CompanyName, SQRT((69.1 * (dbo.zipcodes.Latitude - 32.716263)) * (69.1 * (dbo.zipcodes.Latitude - 32.716263))
+ (69.1 * (dbo.zipcodes.Longitude - - 117.11426) * COS(32.716263 / 57.3)) * (69.1 * (dbo.zipcodes.Longitude - - 117.11426) * COS(32.716263 / 57.3)))
AS Distance
FROM dbo.company INNER JOIN
dbo.CompanyXSignType ON dbo.company.CompanyID = dbo.CompanyXSignType.companyID INNER JOIN
dbo.zipcodes ON dbo.company.Zip = dbo.zipcodes.ZIPCode INNER JOIN
dbo.radius ON dbo.company.radiusID = dbo.radius.radiusID INNER JOIN
dbo.signTypes ON dbo.CompanyXSignType.signtypeID = dbo.signTypes.SignTypeID

I am trying to add this code to the mix, but it will not accept it:
TooFar = CASE WHEN[distance] > [radius].[radius] THEN 'no' ELSE 'good' END

This is the error I get: The Query Designer does not support the CASE SQL construct.

If I try to add it to the "column" area, it spits out the code as a text string.

I am new to SQL Server 2000, so any help would be appreciated. I found some articles on creating a function and using the case statement, but I have no idea how to do that or then, how to call it in my query...

[Dan_Bracuk]

select case
when this then that
when these then those
else somethingelse end

[ssailer]

I don't think I understand. Is this what you're telling me?

SELECT CASE WHEN[distance] > [radius].[radius] THEN 'no' ELSE 'good' AS TooFar END

[paross1]

More like this:
CASE WHEN [distance] > [radius].[radius] THEN 'no' ELSE 'good' END AS TooFar

rather than this:
CASE WHEN[distance] > [radius].[radius] THEN 'no' ELSE 'good' AS TooFar END

Phil

[ssailer]

I can't use the CASE statement in the Query Deisgner. I get the following error message:

The Query Designer does not support the CASE SQL construct.

[insuractive]

I'm not sure you even need the brackets (distance and radius are not reserved words in MSSQL):

'TooFar' = Case When Distance > radius.radius Then 'no' else 'good' end

If that doesn't work, try recreating your replacing the text "distance" with the whole Distance calculation code and see if that works.

[paross1]

Oh, that is right, you can't use the distance alias in the CASE statement like that.. you would have to use the entire calculation.

Phil

[ssailer]

I actually got it to work - finally!!!

TooFar = CASE WHEN Sqrt((69.1*([zipcodes].[latitude]- #passedzip.latitude#))*(69.1*([zipcodes].[latitude]- #passedzip.latitude#))+(69.1*([zipcodes].[longitude]- #passedzip.longitude#)*Cos(#passedzip.latitude#/57.3)*(69.1*([zipcodes].[longitude]- #passedzip.longitude#)*Cos(#passedzip.latitude#/57.3)))) > [radius].[radius] THEN 'No' Else 'Good' END

[ssailer]

I also had to use the Query Analyzer instead of trying to create the view within the Enterprise Manager

[ssailer]

Thanks everybody for your help!