Is there a way to do subqueries inside a case expression in Microsoft SQL Server 2017 (v14)?-CodePudding

I'm moving from PostgreSQL to SQL Server 2017 (v14) and need to rewrite some queries. One of the biggest (reduced down here) uses CASE statement with subqueries.

SELECT 
    SUM(CASE 
            WHEN (call_legs.to_phone_number NOT IN (SELECT CONCAT('1', phone_number)  
                                                    FROM directory 
                                                    WHERE description = 'OEM')) 
                THEN 1 
                ELSE 0 
        END) AS non_oem_out 
FROM 
    call_legs 
WHERE 
    leg_order = 0 
    AND type = 'Voice' 
    AND result != 'Blocked' 
    AND start_time >= '2022-10-09 12:00:00 AM' 
    AND start_time <= '2022-10-16 11:59:00 PM'

This works fine in PostgreSQL but in SQL Server, I get an error

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

My question: is there a way to do subqueries inside a CASE statement in SQL Server?

CodePudding user response：

Direct approach: Using a CTE to cover up for bad table design in table directory.

Transform necessary lookup column to required format temporarily (first CTE part)
Lookup from origin to CTE to decide to sum 1 or 0 value

Code Segment:

WITH
  cteDirectory
AS
(
  SELECT
    CONCAT( '1', phone_number ) AS to_phone_number
  FROM
    directory
  WHERE
    description = 'OEM' -- DO NOT USE reserved words!!!
)
SELECT 
  SUM( CASE WHEN dir.to_phone_number IS NULL THEN 1 ELSE 0 END
  ) AS non_oem_out 
FROM 
  call_legs AS cl
  LEFT OUTER JOIN cteDirectory AS dir
    ON  dir.to_phone_number = cl.to_phone_number
WHERE cl.leg_order = 0 
  AND cl.type = 'Voice' -- DO NOT USE reserved words!!!
  AND cl.result != 'Blocked' 
  AND cl.start_time >= '2022-10-09 12:00:00 AM' 
  AND cl.start_time <= '2022-10-16 11:59:00 PM'

Better approach is to add a derived column to table directory that executes the same deterministic function CONCAT. Then outer join both tables and do the same Case for calculation.