Case statement in where clause, what's wrong with my query?-CodePudding

Can anyone tell me what's wrong with my code?

SELECT 
    *
FROM    
    TABLE.NAME
WHERE 
    TICKET_GROUP IN 
        CASE WHEN ticket_created_at <= CAST('2022-04-01' AS DATE) 
                 THEN  (1500003613882, 360013692813, 114095263234) 
                 ELSE (1500001610322, 360005931333, 1260811380530) 
        END

I get this error

line 8:20: mismatched input 'IN'. Expecting: 'AND', 'EXCEPT', 'FETCH', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OFFSET', 'OR', 'ORDER', 'UNION', <EOF>

I tried changing it to '=' instead of IN but it didn't worked.

CodePudding user response：

A CASE expression always produces a value, never another expression. Therefore you must structure the WHERE clause more like this:

WHERE 
   ( ticket_created_at <= CAST('2022-04-01' AS DATE) 
     AND TICKET_GROUP IN (1500003613882, 360013692813, 114095263234) 
   )
   OR (
     ticket_created_at > CAST('2022-04-01' AS DATE)
     AND TICKET_GROUP IN (1500001610322, 360005931333, 1260811380530) 
   )

Additionally, we can use NULLIF() to simplify the CASE expression in the SELECT clause like so:

SELECT 
    *,
    COALESCE(NULLIF(C_220_COUNTRY_CODE, 'N/A'), COUNTRY_SALES_POD) AS FINAL_COUNTRY_CODE

FROM    
    CUSTOMER_OPS_HISTORICAL.ZENDESK_HISTORICAL_TICKET_CONSOLIDATED_CUSTOMER_OPS_DASHBOARD
WHERE 
   ( ticket_created_at <= CAST('2022-04-01' AS DATE) 
     AND TICKET_GROUP IN (1500003613882, 360013692813, 114095263234) 
   )
   OR (
     ticket_created_at > CAST('2022-04-01' AS DATE)
     AND TICKET_GROUP IN (1500001610322, 360005931333, 1260811380530) 
   )

CodePudding user response：

If you want to "dynamically" define the IN values then you can move TICKET_GROUP IN inside the case statement's result expressions:

WHERE 
    CASE 
       WHEN ticket_created_at <= CAST('2022-04-01' AS DATE) 
          THEN TICKET_GROUP IN (1500003613882, 360013692813, 114095263234) 
       ELSE TICKET_GROUP IN (1500001610322, 360005931333, 1260811380530) 
    END

Or just use the boolean logic with OR and AND:

WHERE 
    (ticket_created_at <= CAST('2022-04-01' AS DATE) AND TICKET_GROUP IN (1500003613882, 360013692813, 114095263234))
    OR (TICKET_GROUP IN (1500001610322, 360005931333, 1260811380530))