SELECT CASE WHEN col1 IN (5,6,7,8) and col2 >= 78
THEN 'A'
ELSE 'B'
END
FROM your_table_name;
Justin 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,I need to write decode for multipe columns for exampleAFAK, we can't use Relational Operators(>,>=,<,<=,<>) with DECODE.
decode (col1 in (5,6,7,8) and col >=78, 'A','B')
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;
user7690206 wrote:Why do you need to use DECODE? The only reasons I can think of are:
Yes, I know that case works for me, But I want to use DECODE in a tool.
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 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. 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.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