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

How to decode for multipe columns with multiple values

942728 Newbie
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
    Justin Cave Oracle ACE
    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
    Most Wanted!!!! Journeyer
    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
    ranit B Expert
    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
    BluShadow Guru Moderator
    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
    942728 Newbie
    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
    942728 Newbie
    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
    Frank Kulash Guru
    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.

    If you can explain your business requirements, someone can help you meet them.
  • 8. Re: How to decode for multipe columns with multiple values
    AlanWms Journeyer
    Currently Being Moderated
    What tool are you using that allows DECODE but not CASE?

    If we had more information we might be able to help you better.
  • 9. Re: How to decode for multipe columns with multiple values
    John Spencer Oracle ACE
    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
    KeithJamieson Expert
    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
    Justin Cave Oracle ACE
    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
    ascheffer Expert
    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
  • Helpful Answers - 5 points