Case .. When Expression in Oracle SQL

CASE .. WHEN expression in Oracle SQL

You could use an IN clause

Something like

SELECT
  status,
  CASE
    WHEN STATUS IN('a1','a2','a3')
    THEN 'Active'
    WHEN STATUS = 'i'
    THEN 'Inactive'
    WHEN STATUS = 't'
    THEN 'Terminated'
  END AS STATUSTEXT
FROM
  STATUS

Have a look at this demo

SQL Fiddle DEMO

Oracle SQL - CASE syntax

Yes, you are right, it is unfortunately not possible to have conditions in the short syntax.

The documentation
calls the short syntax "simple_case_expression"

CASE expr WHEN comparision_expr THEN return_expr

Oracle SQL - SELECT CASE WHEN column = ( select statement)

You simply want to match for the current year. Then one option would be using TO_CHAR(,'yyyy') :

SELECT CASE
         WHEN column1 = TO_CHAR(sysdate,'yyyy') then
          'up_year'
         ELSE
          'past_year'
       END AS "My Year"
  FROM tab   

oracle sql - select statement with multiple case when and check for contains text

In Oracle string literals need to be surrounded in single quotes.

To find a sub-string match you can either use LIKE:

SELECT  ID,
        NAME,
        CASE WHEN Descr LIKE '%Test%'  THEN 'Contains Test'
             WHEN Descr LIKE '%Other%' THEN 'Contains Other'
             ELSE 'No Match'
        END AS Match
FROM    Item i
        LEFT OUTER JOIN
        Description d
        ON i.id = d.item_id

or INSTR():

SELECT  ID,
        NAME,
        CASE WHEN INSTR( Descr, 'Test' ) > 0  THEN 'Contains Test'
             WHEN INSTR( Descr, 'Other' ) > 0 THEN 'Contains Other'
             ELSE 'No Match'
        END AS Match
FROM    Item i
        LEFT OUTER JOIN
        Description d
        ON i.id = d.item_id

or REGEXP_LIKE():

SELECT  ID,
        NAME,
        CASE WHEN REGEXP_LIKE( Descr, 'Test' )  THEN 'Contains Test'
             WHEN REGEXP_LIKE( Descr, 'Other' ) THEN 'Contains Other'
             ELSE 'No Match'
        END AS Match
FROM    Item i
        LEFT OUTER JOIN
        Description d
        ON i.id = d.item_id

Oracle SQL only: Case statement or exists query to show results based on condition

is this what you're looking for ?

In your conditions you're saying:
"If Person has Role "AAA" or "BBB" and not "Auth" then MYAccess column should show value as "Add" for that Person. All other values should be null.
"
Scott has Auth so this condition yields false. According to this rule Scott should not have "Add". In your expected output it has "Add". Am I missing something ?

WITH person_roles (id, person_id, role_id) AS
(
SELECT  1, 1,1 FROM DUAL UNION ALL
SELECT  2, 2,2 FROM DUAL UNION ALL
SELECT  3, 2,3 FROM DUAL UNION ALL
SELECT  4, 2,4 FROM DUAL UNION ALL
SELECT  5, 3,1 FROM DUAL UNION ALL
SELECT  6, 3,5 FROM DUAL UNION ALL
SELECT  7, 4,3 FROM DUAL UNION ALL
SELECT  8, 5,6 FROM DUAL UNION ALL
SELECT  9, 6,3 FROM DUAL UNION ALL
SELECT 10, 6,6 FROM DUAL UNION ALL
SELECT 11, 6,2 FROM DUAL UNION ALL
SELECT 12, 7,5 FROM DUAL UNION ALL
SELECT 13, 7,6 FROM DUAL
),persons (id, person) AS
(
SELECT   1,'John' FROM DUAL UNION ALL   
SELECT   2,'Scott' FROM DUAL UNION ALL  
SELECT   3,'Ruth' FROM DUAL UNION ALL   
SELECT   4,'Smith' FROM DUAL UNION ALL  
SELECT   5,'Frank' FROM DUAL UNION ALL  
SELECT   6,'Martin' FROM DUAL UNION ALL 
SELECT   7,'Blake' FROM DUAL
),roles (id, role) AS
(
SELECT 1,'JJJ' FROM DUAL UNION ALL   
SELECT 2,'Auth' FROM DUAL UNION ALL  
SELECT 3,'AAA' FROM DUAL UNION ALL   
SELECT 4,'MMM' FROM DUAL UNION ALL   
SELECT 5,'KKK' FROM DUAL UNION ALL   
SELECT 6,'BBB' FROM DUAL 
), rule_1(person_id, role_type)  AS
(
SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) 
  FROM person_roles pr 
  JOIN persons p ON p.id = pr.person_id 
  JOIN roles r ON r.id = pr.role_id
  GROUP BY p.id
)
SELECT p.person, r.role, 
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END as action
  FROM person_roles pr 
  JOIN persons p ON p.id = pr.person_id 
  JOIN roles r ON r.id = pr.role_id
  JOIN rule_1 rl ON rl.person_id = pr.person_id
ORDER BY p.person

PERSON ROLE ACTION
------ ---- ------
Blake  BBB  Add   
Blake  KKK        
Frank  BBB  Add   
John   JJJ        
Martin AAA        
Martin Auth Remove
Martin BBB        
Ruth   KKK        
Ruth   JJJ        
Scott  MMM        
Scott  AAA        
Scott  Auth Remove
Smith  AAA  Add   

If you want to avoid the CTE (with clause) you can replace the last 2 statements with 1:

SELECT p.person, r.role, 
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END as action
  FROM person_roles pr 
  JOIN persons p ON p.id = pr.person_id 
  JOIN roles r ON r.id = pr.role_id
  JOIN (
    SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
    FROM person_roles pr 
    JOIN persons p ON p.id = pr.person_id 
    JOIN roles r ON r.id = pr.role_id
    GROUP BY p.id
    ) rl ON rl.id = pr.person_id
ORDER BY p.person

Oracle SQL: Case statement

I think this is the query you are looking for:

SELECT DISTINCT
    m.metric_id         AS metric_id,
    m.subject_abbrevn   AS p_subj,
    m.table_abbrevn     AS cnt_subj,
    c.p_id              AS p_id,
    c.cnt_id            AS cnt_id,
    c.gl_id             AS gl_id,
FROM croms_qc_currency_1_v c
INNER JOIN T_CROMS_CAT_T m ON DECODE(c.cnt_subj, 'MEPC_PL', 6135, NULL) = m.metric_id;

Do you actually need the DISTINCT? Is there more than one row with those IDs?
Also, you can expand this if you have other CNT_SUBJ values which you need to translate.

How to write a case statement in the Where clause of Oracle SQL?

This would be simpler phrased with boolean logic:

where (flag_a = 'P' and flag_b = 0)
   or (flag_a = 'C' and flag_b = 1)
   or (flag_a = 'B' and flag_b in (0, 1))

We can factorize a little:

where (flag_a in ('B', 'P') and flag_b = 0)
   or (flag_a in ('B', 'C') and flag_b = 1)

Or we can use tuple equality:

where (flag_a, flag_b) in (('P', 0), ('C', 1), ('B', 0), ('B', 1))

flag_b looks like a number, so I compared it against literal numbers; if that's really a string, then you can add back the single quotes around the literals.

Using Case statement in Where clause in Oracle SQL

Your question is a bit ambiguous. I have assumed that country is an attribute in the table employees of data type VARCHAR.

SELECT * FROM employees
WHERE 
    (emp_id = v_emp_id AND country = 'USA') 
    OR (emp_id <= v_emp_id AND country != 'USA')

You might want to take a look at WHERE, OR and AND.

Quoting the OR page linked above:

If you use multiple logical operators in a statement, Oracle evaluates the OR operators after the NOT and AND operators. However, you can change the order of evaluation by using parentheses.

---

Related Topics