Search code examples
selectconditional-statementscasewhere-clauseoracle12c

Using Case statement in Where clause in Oracle SQL

I have a select statement like below

select * 
from employees 
where emp_id <= v_emp_id;

I want this emp_id to be <= v_emp_id if country is not USA.

If country is USA, then I want emp_id to be = v_usa_emp_id.

Solution

  • 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.