Using IIF in Access

I am currently moving a client from an Access DB to MS SQL Server 2008, and came up to a problem when I had to move a query to a view.  The query:

[sql]SELECT Client.CompanyName, Client.Address, Client.Name, IIf ([Quote].[QuoteID] Is Null, 0, [Quote].[QuoteID]) AS QuoteID
FROM Client LEFT JOIN Quote ON Client.ClientID = Quote.ClientID;[/sql]

Using CASE in TSQL

Use the CASE expression

[sql]SELECT Client.CompanyName, Client.Address, Client.Name, CASE WHEN [Quote].[QuoteID] Is Null THEN 0 ELSE [Quote].[QuoteID] END AS QuoteID
FROM Client LEFT JOIN Quote ON Client.ClientID = Quote.ClientID;[/sql]

Thanks to: http://www.sql-server-performance.com/faq/iif_tsql_p1.aspx

Leave a Reply