12 Replies Latest reply: Nov 30, 2012 10:03 AM by ascheffer

# How to decode for multipe columns with multiple values

Hi,

I need to write decode for multipe columns for example

decode (col1 in (5,6,7,8) and col >=78, 'A','B')

I really appreciate any help....

Thanks-
• ###### 1. Re: How to decode for multipe columns with multiple values
Is there a reason that it has to be a DECODE rather than a CASE?
``````SELECT CASE WHEN col1 IN (5,6,7,8) and col2 >= 78
THEN 'A'
ELSE 'B'
END
FROM your_table_name;``````
Justin
• ###### 2. Re: How to decode for multipe columns with multiple values
or if u need that only in the decode statement then
``````WITH t AS
(SELECT 5 AS a, 79 AS b
FROM DUAL
UNION ALL
SELECT 6, 78
FROM DUAL
UNION ALL
SELECT 7, 70
FROM DUAL
UNION ALL
SELECT 8, 79
FROM DUAL)
SELECT DECODE (CASE
WHEN a IN (5, 6, 7, 8) AND b >= 78
THEN 0
ELSE 1
END, 0, 'A', 'B')
FROM t;``````
Regards,
friend
• ###### 3. Re: How to decode for multipe columns with multiple values
I need to write decode for multipe columns for example

decode (col1 in (5,6,7,8) and col >=78, 'A','B')
AFAK, we can't use Relational Operators(>,>=,<,<=,<>) with DECODE.

Use DECODE outside and CASE for '>=' check.
• ###### 4. Re: How to decode for multipe columns with multiple values
most wanted!!!! wrote:
or if u need that only in the decode statement then
``````WITH t AS
(SELECT 5 AS a, 79 AS b
FROM DUAL
UNION ALL
SELECT 6, 78
FROM DUAL
UNION ALL
SELECT 7, 70
FROM DUAL
UNION ALL
SELECT 8, 79
FROM DUAL)
SELECT DECODE (CASE
WHEN a IN (5, 6, 7, 8) AND b >= 78
THEN 0
ELSE 1
END, 0, 'A', 'B')
FROM t;``````
?

That's not only in a decode statement, as you've used CASE within it. In which case you may as well just use a CASE statement in the first place.
• ###### 5. Re: How to decode for multipe columns with multiple values
Thanks for the information on decode doesn't support relational operator.
• ###### 6. Re: How to decode for multipe columns with multiple values
Yes, I know that case works for me, But I want to use DECODE in a tool.
• ###### 7. Re: How to decode for multipe columns with multiple values
Hi,
user7690206 wrote:
Yes, I know that case works for me, But I want to use DECODE in a tool.
Why do you need to use DECODE? The only reasons I can think of are:
(1) You're using Oracle 8.0 (or earlier)
(2) This is a school assignment, and the instructions specifically say you must use DECODE.

• ###### 8. Re: How to decode for multipe columns with multiple values
What tool are you using that allows DECODE but not CASE?

• ###### 9. Re: How to decode for multipe columns with multiple values
It is possible to to range tests and in lists using decode, but it gets really ugly really quickly. This is a decode equivalent of the case solution others have already shown.
``````SQL> with t as (
2     select 77 col, 5 col1 from dual union all
3     select 78, 5 from dual union all
4     select 100, 8 from dual union all
5     select 100, 9 from dual union all
6     select 77, 6 from dual union all
7     select 78, 4 from dual union all
8     select 90, 7 from dual)
9  select col, col1,
10         decode(sign(col - 77), 1,
11            decode(sign(col1 - 4), 1,
12               decode(sign(col1 - 9), -1, 'A',
13               'B'),
14            'B'),
15         'B') decode_calc,
16         case when col1 in (5, 6, 7, 8) and
17                   col >= 78 then 'A'
18              else 'B' end case_calc
19  from t;

COL       COL1 D C
---------- ---------- - -
77          5 B B
78          5 A A
100          8 A A
100          9 B B
77          6 B B
78          4 B B
90          7 A A``````
The only reason it is a compact as it is (for certain definitions of compact) is that your in list is essentially a range comparision (col1 >= 5 and col1 <= 8). If instead, you had something like col1 in (5, 8, 9) the decode version would look like this, while the case version stays essentially the same,
``````SQL> with t as (
2     select 77 col, 5 col1 from dual union all
3     select 78, 5 from dual union all
4     select 100, 8 from dual union all
5     select 100, 9 from dual union all
6     select 77, 6 from dual union all
7     select 78, 4 from dual union all
8     select 90, 7 from dual)
9  select col, col1,
10         decode(sign(col - 77), 1,
11            decode(col1, 5, 'A',
12               decode(col1, 8, 'A',
13                  decode(col1, 9, 'A',
14                  'B')
15               )
16            ),
17         'B') decode_calc,
18         case when col1 in (5, 8, 9) and
19                      col >= 78 then 'A'
20                 else 'B' end case_calc
21  from t;

COL       COL1 D C
---------- ---------- - -
77          5 B B
78          5 A A
100          8 A A
100          9 A A
77          6 B B
78          4 B B
90          7 B B``````
John
• ###### 10. Re: How to decode for multipe columns with multiple values
OK, it can be done but not easily.

You have to use a combination of decode , sign and instr

``````WITH mytable
AS
(
SELECT 5  COL1, 78 COL2  FROM DUAL UNION ALL
SELECT 6 COL1, 77 COL2  FROM DUAL UNION ALL
SELECT 7 COL1, 79 COL2  FROM DUAL UNION ALL
SELECT 8 COL1, 80 COL2 FROM DUAL union all
SELECT 9 COL1, 80 COL2 FROM DUAL UNION ALL
SELECT 10 COL1, 76 COL2 FROM DUAL
)
SELECT
COL1, DECODE(SIGN(INSTR('5,6,7,8',COL1)),1,'PRESENT','NOT PRESENT'),
COL2,
decode(sign(col2-78),-1,'Less than 78','Greater or Equal to 78')
from mytable``````
It is however far easier using case.
• ###### 11. Re: How to decode for multipe columns with multiple values
If you are really determined to use a DECODE, it will be a much uglier expression. Are you really sure that your tool needs to use a DECODE rather than something more reasonable?

You could do something like
``````DECODE( sign( col2 - 78 ),
0, decode( col1, 5, 'A', 6, 'A', 7, 'A', 8, 'A', B' ),
1, decode( col1, 5, 'A', 6, 'A', 7, 'A', 8, 'A', B' ),
'B' )``````
Of course, I would hate to be the poor developer that got stuck with maintaining that DECODE rather than using a nice, simple, easy-to-follow CASE statement.

Justin
• ###### 12. Re: How to decode for multipe columns with multiple values
``````WITH t AS
(SELECT 5 AS col1, 79 AS col
FROM DUAL
UNION ALL
SELECT 6, 78
FROM DUAL
UNION ALL
SELECT 7, 70
FROM DUAL
UNION ALL
SELECT 8, 79
FROM DUAL)
select col1, col
, decode( 1, ( select 1 from dual where col1 in (5,6,7) and col>= 78 ), 'A', 'B' )
from t``````