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

    How to decode for multipe columns with multiple values

    942728
      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
          JustinCave
          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!!!!
            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
              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
                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
                  Thanks for the information on decode doesn't support relational operator.
                  • 6. Re: How to decode for multipe columns with multiple values
                    942728
                    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
                      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
                        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
                          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
                            Keith Jamieson
                            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
                              JustinCave
                              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
                                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