This discussion is archived
12 Replies Latest reply: Nov 30, 2012 8:03 AM by ascheffer

# How to decode for multipe columns with multiple values

Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
Thanks for the information on decode doesn't support relational operator.
• ###### 6. Re: How to decode for multipe columns with multiple values
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
What tool are you using that allows DECODE but not CASE?

• ###### 9. Re: How to decode for multipe columns with multiple values
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
``````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``````

#### Legend

• Correct Answers - 10 points