Skip to main content
Inspiring
April 10, 2007
Question

IIF and SQL Server 2000

  • April 10, 2007
  • 7 replies
  • 853 views
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...
This topic has been closed for replies.

7 replies

ssailerAuthor
Inspiring
April 10, 2007
Thanks everybody for your help!
ssailerAuthor
Inspiring
April 10, 2007
I also had to use the Query Analyzer instead of trying to create the view within the Enterprise Manager
ssailerAuthor
Inspiring
April 10, 2007
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
Inspiring
April 10, 2007
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.
Participating Frequently
April 10, 2007
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
ssailerAuthor
Inspiring
April 10, 2007
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.
ssailerAuthor
Inspiring
April 10, 2007
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
Participating Frequently
April 10, 2007
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
Inspiring
April 10, 2007
select case
when this then that
when these then those
else somethingelse end