Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Using Decode to return multiple strings

    Hi,

    I'm using the DECODE function inside a subquery that is meant to potentially return STRING values. The outer query has an IN clause that relies on the STRING being returned from the DECODE. The problem is that for some reason, DECODE wont let me return multiple or concatenated strings.

    This is my table:

    ----------------------
    DEPT_ID | DEPT_NAME
    ----------------------
    11 | sales
    12 | marketing
    ----------------------

    When I run,
    Code:
    select DECODE(0,
                    0,('''sales'',''marketing'''), 
                    1,11, 
                    2,'sales' 
                ) 
    from dual
    ....it returns 'sales','marketing'.

    But when I try to nest this inside an outer query that uses the IN clause, it doesn't return anything. For example,

    Code:
    select * from dept where dept_name in (
        select DECODE(0,
                        0,('''sales'',''marketing'''), 
                        1,11, 
                        2,'sales' 
                    ) 
        from dual
    )
    ....returns nothing.

    Am I using decode incorrectly? I've tried several ways to do this (CASE instead of DECOE, CONCAT in the inner query) without success.

    I also read somewhere that returning multiple strings like this was not possible with DECODE (although it does work with numbers...I tried it).

    Looking for suggestions.

  2. #2
    Join Date
    Mar 2007
    Posts
    615
    Quote Originally Posted by PrashantSehgal View Post
    ....it returns 'sales','marketing'.

    But when I try to nest this inside an outer query that uses the IN clause, it doesn't return anything.
    Because IN clause contains only one argument - the string ''sales','marketing''. It is probably not present in any row, so the query returns correct result.
    Quote Originally Posted by PrashantSehgal View Post
    Am I using decode incorrectly? I've tried several ways to do this (CASE instead of DECOE, CONCAT in the inner query) without success.

    I also read somewhere that returning multiple strings like this was not possible with DECODE (although it does work with numbers...I tried it).

    Looking for suggestions.
    Do not use string, use collection type. E.g. something like this (based on modified Oracle DEPT table from HR - used VARCHAR2 instead of CHAR types):
    Code:
    CREATE TABLE DEPT (
     DEPTNO              NUMBER(2) NOT NULL,
     DNAME               VARCHAR2(14),
     LOC                 VARCHAR2(13),
     CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));
    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
    INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
    INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
    INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
    
    create type t_varchar2 as table of varchar2(4000);
    /
    
    SQL> select * from dept where dname in ( select column_value
      2    from table( DECODE(0,0,t_varchar2('SALES','ACCOUNTING')
      3                        ,1,t_varchar2('ACCOUNTING')
      4                        ,2,t_varchar2('SALES')
      5                        ,t_varchar2()) ) );
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            30 SALES          CHICAGO
            10 ACCOUNTING     NEW YORK
    In some old Oracle versions, it might be necessary to use CAST function to specify collection type.
    For more information, have a look at SQL Reference, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Not sure if I see exactly what you're trying to do. I almost looks like you're trying to create a lookup table on-the-fly. You could do something like:

    Code:
    WITH lookup_t as (select 0 as CATEGORY, '''sales'',''marketing''' as CATEGORY_DESC from dual
                      union all
                      select 1, '''accounting''' from dual
                      union all
                      select 2, '''sales''' from dual)
    select * 
    from dept
    where dept_no in (select CATEGORY from lookup_t)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •