Using decode in where clause, with "in"

Question

Have a tricky situation in witch I belive you guys can help me out. I want to use decode in my cursors where-clause. I am using "IN()" but I belive the program thinks the comma belongs to the decode and not as a separator between values.

I think maybe using CASE might solve this, am I right?

 CURSOR order_cur (
  cur_ao    VARCHAR2) IS 
  SELECT t1.nr, t1.status$nr
      FROM eh_order_t@NGEXT_DBLINK t1
     WHERE     t1.status$nr IN (3, 6)
           AND t1.ao IN (DECODE (
                            cur_ao,
                            'ALLA', Argus_ehandel_pkg.get_ehorder_ao (
                                       t1.nr),
                            SUBSTR (cur_ao, 1, 2), SUBSTR (cur_ao, 3, 2)))

CASE variant

CURSOR order_cur (
  cur_ao    VARCHAR2) IS
    SELECT t1.nr, t1.status$nr
      FROM eh_order_t@NGEXT_DBLINK t1
     WHERE     t1.status$nr IN (3, 6)
           AND t1.ao IN (CASE
                            WHEN cur_ao = 'ALLA'
                            THEN
                               Argus_ehandel_pkg.get_ehorder_ao (t1.nr)
                            ELSE
                               SUBSTR (cur_ao, 1, 2), SUBSTR (cur_ao, 3, 2)
                         END)                -- SUBSTR (cur_ao, 3, 2) END)
  --AND t1.nr = DECODE (order_in, NULL, t1.nr, order_in)
  ORDER BY t1.skapad_dat ASC;

for sure it does think it belongs to the decode, if you put it inside the decode paranthesis... — SomeJavaGuy, Aug 27 '15 at 08:14

Lalit Kumar B · Accepted Answer · 2015-08-27T09:04:28.800

DECODE is an expression, and it will work in the IN list along with other values in the list, you just need to take care of the syntax and parenthesis.

What matters is the final value that you get after evaluating the DECODE expression. Comma separated values inside DECODE will be evaluated as part of the DECODE expression. And the values after closing the parenthesis of DECODE expression, but inside the IN list will be considered in the IN list.

For example,

SQL> SELECT deptno FROM emp
  2  WHERE deptno IN(decode(ename, 'KING', 20, 10), 30);

    DEPTNO
----------
        30
        30
        30
        30
        10
        30
        30
        10

8 rows selected.

SQL>

There are two comma separated values in the IN list. DECODE expression is evaluated as 10, and 20 is in the IN list. So, the IN list is finally evaluated as:

WHERE deptno = 10 OR deptno = 20

10 is the result of DECODE, and 30 is hardcoded in the IN list.

Update :

OP posted a CASE expression and the same could written using DECODE.

Your CASE expression will throw an error because you are trying to return two values from the CASE expression which is not possible. You can at most return a single value from the expression, just like a function.

It could be written using DECODE in the IN list:

IN(  
DECODE(cur_ao, 'ALLA', Argus_ehandel_pkg.get_ehorder_ao (t1.nr), SUBSTR (cur_ao, 1, 2))), 
SUBSTR (cur_ao, 1, 2)
)

In the above IN list, there are two values, the first is evaluated by the DECODE expression, and the other is evaluated by SUBSTR (cur_ao, 1, 2).

Very good explanation, the result I am trying to achieve is: IF cur_ao = 'ALLA' THEN ao IN (Argus_ehandel_pkg.get_ehorder_ao (t1.nr)) ELSE ao IN (SUBSTR (cur_ao, 1, 2) , SUBSTR (cur_ao, 3, 2)) END IF; — user2362791, Aug 27 '15 at 08:41

Using decode in where clause, with "in"

1 Answers1