If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Using Decode to return multiple strings

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 612
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/
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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)
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On