CODEWITHSUNDEEP
oracle-databaseplsqlcase
Ruslan
Ruslan

Reputation: 423

How to use CASE and IN inside where statement when comparing a variable

I don't want to use dynamic SQL so I'm trying different ways to alter my where statement.

Inside my WHERE statement, there is a row: 

AND c.nregion = pnregion

pnregion is a number of Russian region. This statement helps my cursor to work a lot faster since Oracle doesn't have to look through the full table.

Problem is that if pnregion is equal to 47 or 78 then this row should look like this 

AND c.nregion in (47, 78)

How to do it? Should I use CASE somehow or is there something I could do outside my cursor?

Upvotes: 1

Views: 66

Answers (3)

Ori Marko
Ori Marko

Reputation: 58792

You can use decode function to add another option per codes 47/78:

AND (c.nregion  = pnregion OR c.nregion = decode(pnregion, 47, 78, 78, 47))

Upvotes: 1

MT0
MT0

Reputation: 168106

You can use:

WHERE ( c.nregion = pnregion
      OR (   pnregion  IN ( 47, 78 )
         AND c.nregion IN ( 47, 78 )
         )
      )

This is easily extensible if you want more than 2 values as the values can be added to both IN filters.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142798

If I understood the question, it is a varying elements in IN list that bothers you. In other words, you can't put comma-separated values into an IN list, unless you

  • switch to dynamic SQL (which is what you don't want)
  • separate those values into rows

Here's an example, based on Scott's schema; I hope you'll understand it.

This is what you currently have:

SQL> create or replace procedure p_test (pnregion in varchar2)
  2  is
  3    cursor c1 is select empno, ename from emp
  4      where deptno in pnregion;
  5  begin
  6    for cr in c1 loop
  7      dbms_output.put_line(cr.ename);
  8    end loop;
  9  end;
 10  /

Procedure created.

SQL> -- This is OK
SQL> exec p_test('10');
CLARK
KING
MILLER

PL/SQL procedure successfully completed.

SQL> -- This is not OK
SQL> exec p_test('10, 20');
BEGIN p_test('10, 20'); END;

*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "SCOTT.P_TEST", line 6
ORA-06512: at line 1


SQL>

Now, split comma-separated values to rows and watch it work:

SQL> create or replace procedure p_test (pnregion in varchar2)
  2  is
  3    cursor c1 is select empno, ename from emp
  4      where deptno in (select regexp_substr(pnregion, '[^,]+', 1, level)
  5                       from dual
  6                       connect by level <= regexp_count(pnregion, ',') + 1
  7                      );
  8  begin
  9    for cr in c1 loop
 10      dbms_output.put_line(cr.ename);
 11    end loop;
 12  end;
 13  /

Procedure created.

SQL> -- This is OK
SQL> exec p_test('10');
CLARK
KING
MILLER

PL/SQL procedure successfully completed.

SQL> -- This is also OK now
SQL> exec p_test('10, 20');
SMITH
JONES
CLARK
SCOTT
KING
ADAMS
FORD
MILLER

PL/SQL procedure successfully completed.

SQL> -- Or even some more values:
SQL> exec p_test('10, 20,30');
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions