Convert from Access Query to MS SQL View

[Dave_cha]

Hi folks,

I have a query which pulls together several other queries. It works fine in Access but I also need it to work on a MS SQL server.

the SQL is as follows;

SELECT Client.ClientName, Entity.EntityName, IIf([101].[ProductID] IS NULL, [-], [X]) AS TA, IIf([301].[ProductID] IS NULL, [-], [X]) AS TR, IIf([302].[ProductID] IS NULL,
[-], [X]) AS CU, IIf([304].[ProductID] IS NULL, [-], [X]) AS TC, IIf([502].[ProductID] IS NULL, [-], [X]) AS FA, IIf([503].[ProductID] IS NULL, [-], [X]) AS MO
FROM Client LEFT JOIN
Entity ON Client.ClientID = Entity.ClientID LEFT JOIN
VIEW_PROD301 AS 301 ON Entity.EntityID = [301].EntityID LEFT JOIN
VIEW_PROD302 AS 302 ON Entity.EntityID = [302].EntityID LEFT JOIN
VIEW_PROD304 AS 304 ON Entity.EntityID = [304].EntityID LEFT JOIN
VIEW_PROD502 AS 502 ON Entity.EntityID = [502].EntityID LEFT JOIN
VIEW_PROD503 AS 503 ON Entity.EntityID = [503].EntityID LEFT JOIN
VIEW_PROD101 AS 101 ON Entity.EntityID = [101].EntityID;

The problem seems to be with the iif statement.

Any ideas how I can gt around this?

Thanks,

Dave

 

[FoFa]

You can either create a stored procedure on the SQL server, or change the IIF's to CASE statements, or ISNULL statements.
Example - IIf([101].[ProductID] IS NULL, [-], [X]) AS TA can be changed to
CASE [101].[ProductID] WHEN NULL THEN '[-]' ELSE '[X]') END as TA

 

[Dave_cha]

Thanks guys for the response.

If possible I'd prefer to build the solution as a view in MS SQL.

I tried the CASE option as you described it FoFa however I receive the following error;

"The Query Designer does not support the CASE SQL construct."

Thanks,

Dave