9 Replies Latest reply on Apr 10, 2007 3:11 PM by ssailer

    IIF and SQL Server 2000

    ssailer Level 1
      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...