Conditional Where Clause with Case Statement in Oracle

Conditional WHERE clause with CASE statement in Oracle

You can write the where clause as:

where (case when (:stateCode = '') then (1)
            when (:stateCode != '') and (vw.state_cd in (:stateCode)) then 1
            else 0)
       end) = 1;

Alternatively, remove the case entirely:

where (:stateCode = '') or
      ((:stateCode != '') and vw.state_cd in (:stateCode));

Or, even better:

where (:stateCode = '') or vw.state_cd in (:stateCode)

Oracle with CASE Statement in WHERE clause

Never forget that in Oracle SQL the '' literal (empty string) is the same as NULL, hence your predicate e.ColumnName != '' is equivalent to e.ColumnName != null which always evaluates to NULL.

Second problem is that you are trying output a boolean value from your CASE. No, Oracle can't use boolean expressions as results from functions. (Let's consider CASE to be a function, as I cannot remember a more proper name for these language constructs now.)

Try it this way:

PROCEDURE GetBatchTotals(pEntityName VARCHAR DEFAULT NULL) IS
BEGIN
  -- Sample Query
  SELECT ColumnName
  FROM TableName e
  WHERE
    (pEntityName IS NULL AND e.ColumnName is not null) OR
    (pEntityName IS NOT NULL AND e.ColumnName = pEntityName);
END GetBatchTotals;

There's a few more issues with your code, let's have them fixed:

(1)
Don't use varchar, use varchar2 instead. A strange "enhancement" of Oracle's, don't ask.

(2)
For equality predicates with non-NULL values you may leave off the predicate pEntityName is not null from one of the AND-s, it will be implied.

(3)
Predicate e.ColumnName is not null really filters out the rows where e.ColumnName is NULL, which is something different from your original idea to query all rows regardless of their e.ColumnName value.

(4)
Either loop through the query results or bulk fetch them to a collection or return them as a ref cursor. In PL/SQL you must put the query results into something! Let's loop through the query results and spool the ColumnName contents to server output...

PROCEDURE GetBatchTotals(pEntityName VARCHAR2 DEFAULT NULL) IS
BEGIN
  for rec in (
    SELECT ColumnName
    FROM TableName e
    WHERE pEntityName IS NULL -- Just to select everything in the table
      OR e.ColumnName = pEntityName
  ) loop
    dbms_output.put_line(rec.ColumnName);
  end loop;
END GetBatchTotals;

Using Case statement in Where clause in SQL developer

I don't think you want a case statement. You probably just want

where (accCode in (130,131) and type = 1)
   or (accCode in (230,231) and type = 0)

If you really want to use a case statement, you could say

where (case when accCode in (130,131) then 1 
            when accCode in (230,231) then 0
            else null
         end) = type

But that will not be as readable nor will it be as easy for the optimizer to find a good execution plan.

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