T-SQL – How to Use OR Operator in CASE THEN Statement

caset-sql

I have a situation like this

select col1, col2
from table1
where col1 = case when (booleanexp1) then a OR col1 = b 
                  when (booleanexp1) then c OR col1 = d end

I mean I need to get where col1 = a or col1 = b when booleanexp1 is TRUE,
but I get a syntax error? How is the right way to perform something like this?

Best Answer

Assuming two booleanexp1s is a misprint:

select col1, col2
from table1
where ((booleanexp1) AND (col1 IN (a, b)))
   or ((booleanexp2) AND (col1 IN (c, d)))

PS. not (booleanexp1) is one possible option of (booleanexp2)...

Related Solutions

DB2 – How to Use a CASE Expression in the WHERE Clause

You cannot use the CASE expression in DB2 this way. The result of a CASE expression cannot be a boolean value. Your WHERE clause might look something like this, if one were to blindly translate your code:

WHERE
    COUNTRY_CD = '81930' AND 
    LANG_CD = '02' AND (
      ( PARM_ADTR_ID = 'ALL'  
        AND (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%') ) 
       OR
      ( (PARM_ADTR_ID != 'ALL' OR PARM_ADTR_ID IS NULL)  
        AND AS_ADTR_ID_P LIKE LOC_ADTR_ID )
    ) AND 
    DEALER_CDE_VEGA LIKE '8%'

However, if you remove the redundant conditions it can be simplified like so (HT to ypercubeᵀᴹ, who wakes earlier:)

WHERE
    COUNTRY_CD = '81930' AND 
    LANG_CD = '02' AND (
      PARM_ADTR_ID = 'ALL' OR AS_ADTR_ID_P LIKE LOC_ADTR_ID
    ) AND 
    DEALER_CDE_VEGA LIKE '8%'

ORACLE – Trying to return MAX or MIN based on a CASE statement

When you want to display aggregate results for each row without grouping, use the analytic versions of standard aggregate functions. For example:

SELECT  
  CASE mod(ROLE_TYPE, 2)
    WHEN 1 THEN MAX(SALARY) OVER ()
    ELSE MIN(SALARY) OVER ()
  END
FROM HR;

Best Answer

Related Solutions

DB2 – How to Use a CASE Expression in the WHERE Clause

ORACLE – Trying to return MAX or MIN based on a CASE statement

Related Question