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 QuoteIDFROM 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 QuoteIDFROM Client LEFT JOIN Quote ON Client.ClientID = Quote.ClientID;[/sql]
Thanks to: http://www.sql-server-performance.com/faq/iif_tsql_p1.aspx