how to convert access IIF and Dlookup function to SQL Server

Question

Hello

Ive recently migrated an access database to SQL Server. However I also have to convert all access queries to SQL Server and come across some queries with IIF and Dlookup functions .and cant get this working in SQL . Code is here and this is the CASE statement being used and a join for the lookup . can someone please help to get the CASE statement working or any other solution that might work
Thank you

a)
--IIf([Capability Now with RAG_Crosstab].[Other2] Is Null,Null,DLookUp("OtherCapabilityDesc","Capability With RAG","QTRID =
--" & [Capability Now with RAG_Crosstab].[QTRID] & "AND ProjReturnID
--= " & [Capability Now with RAG_Crosstab].[ProjReturnID] & "And Capability = 'Other2'")) AS [Other2 - Name],
----[Capability Now with RAG_Crosstab].Other2, [Capability Future with RAG_Crosstab].Other2 AS [Other2 - Future],

b)

CASE
WHEN [vwCapability Now with RAG_Crosstab].[Other1] Is Null THEN NULL ELSE

(SELECT
r.OtherCapabilityDesc
FROM
[vwCapability With RAG] as r
WHERE
r.QTRID = [vwCapability Now with RAG_Crosstab].QTRID
AND
r.ProjReturnID = [vwCapability Now with RAG_Crosstab].ProjReturnID
AND
r.Capability = 'Other1'
)
END
AS [Other1 - Name],
[vwCapability Now with RAG_Crosstab].Other1,
[vwCapability Future with RAG_Crosstab].Other1 AS [Other1 - Future

Answer 1

SQL Server (starting with 2012) also has the IIF() function. The syntax is
IIF(condition, value_if_true, value_if_false)

I think the DLookup Function in Access is equivalent to the SELECT statement in SQL Server.
DLookUp("OtherCapabilityDesc","Capability With RAG","QTRID = '')

SELECT OtherCapabilityDesc FROM [Capability With RAG] WHERE QTRID = '';

SELECT  IIF (r.[Other1] IS NULL, NULL, r.OtherCapabilityDesc) AS [Other1 - Name], 
 nr.[Other1], 
 fr.Other1 AS [Other1 - Future]
FROM [vwCapability With RAG] AS r
INNER JOIN [vwCapability Now with RAG_Crosstab] AS nr 
ON r.QTRID = nr.QTRID AND r.ProjReturnID = nr.ProjReturnID
INNER JOIN [vwCapability Future with RAG_Crosstab] AS fr
ON r.QTRID = fr.QTRID AND r.ProjReturnID = fr.ProjReturnID
WHERE r.Capability = 'Other1';

Answer 2

Hi,
Note that CASE is an expression, not a statement. Therefore, it does not support you to control the flow of activities or do some damage based on conditional logic. It can be used in certain situations: SELECT, where, like and order in BY clauses and in CHECK.

Select [Other1 - Name],[Other1 -Now],  [Other1 - Future]  
  Case When N.[Other1] is Null Then Null else  
    (SELECT  
    r.OtherCapabilityDesc  
    FROM r  
    WHERE  
    r.QTRID =N.QTRID  
    AND  
    r.ProjReturnID = N.ProjReturnID  
    AND  
    r.Capability = 'Other1' )  
    END as [Other1 - Name], N.Other as [Other1 -Now], F.Other1 AS [Other1 - Future]  
From [vwCapability With RAG] as r,[vwCapability Now with RAG_Crosstab] as N,[vwCapability Future with RAG_Crosstab] as F;  

Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 3

SELECT  
    CASE WHEN r.[Other1] IS NULL THEN NULL ELSE r.OtherCapabilityDesc END AS [Other1 - Name], 
    nr.[Other1], 
    fr.Other1 AS [Other1 - Future]
FROM [vwCapability With RAG] AS r
INNER JOIN [vwCapability Now with RAG_Crosstab] AS nr 
ON r.QTRID = nr.QTRID AND r.ProjReturnID = nr.ProjReturnID
INNER JOIN [vwCapability Future with RAG_Crosstab] AS fr
ON r.QTRID = fr.QTRID AND r.ProjReturnID = fr.ProjReturnID
WHERE r.Capability = 'Other1';