Best way to do a IIF conditional with subquery

I have a select with subquery inside like

SELECT
....
 , (SELECT TOP 1
                        [DDC].[ContractedAmount]
                        FROM @CustomersTable AS [DDC]
                        WHERE [DDC].[DesignKey] = [D].[DesignKey]) AS [Contracted Amount]
     FROM....

But sometimes value of this select can be 0.00 so I want to add conditional if it’s 0.00 just return NULL, so I do an IIF conditional like:

     , IIF((SELECT TOP 1
                            [DDC].[ContractedAmount]
                            FROM @CustomersTable AS [DDC]
                            WHERE [DDC].[DesignKey] = [D].[DesignKey]) = 
                            0.00,
                            NULL,
           (SELECT TOP 1
                            [DDC].[ContractedAmount]
                            FROM @CustomersTable AS [DDC]
                            WHERE [DDC].[DesignKey] = [D].[DesignKey])) AS [Contracted Amount]

But as you can see I need to do another subquery in subquery, so I think this is not the best practice to achieve that? is there another way to do this more easy and readeable. Any suggestions? Regards

Answer

You could use NULLIF:

Returns a null value if the two specified expressions are equal.

SELECT ...
  NULLIF((subquery), 0.00) AS [Contracted Amount]

Another way is to use CROSS/OUTER APPLY:

SELECT ...,
    IIF(sub.ContractedAmount = 0.00, NULL, sub.ContractedAmount) AS [Contracted Amount]
FROM tab
OUTER APPLY (SELECT TOP 1 [DDC].[ContractedAmount]
            FROM @CustomersTable AS [DDC]
            WHERE [DDC].[DesignKey] = [D].[DesignKey])) sub

Advertisement

Answer