how to convert access IIF and Dlookup function to SQL Server

your mums trim 1 Reputation point
2020-11-28T00:37:13.573+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2020-11-28T04:54:32.013+00:00

    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';
    

  2. Joyzhao-MSFT 15,566 Reputation points
    2020-11-30T07:55:10.727+00:00

    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

    0 comments No comments

  3. Guoxiong 8,126 Reputation points
    2020-11-30T22:48:10.283+00:00
    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';
    
    0 comments No comments