Access to SQL Server

Using IIf in TSQL

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:

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;

Using CASE in TSQL

Use the CASE expression

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;

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

Leave a Reply

Your email address will not be published. Required fields are marked *