1 2 Previous Next 16 Replies Latest reply: Oct 9, 2012 8:42 AM by Kim Berg Hansen RSS

    Case not working for non numeric data

    967088
      Hi All,

      I want to check for a column value, if its non numeric then i should retrieve data as it is else i should check for other conditions.

      My query is:

      select bitrate,case
      when bitrate >=1000000 then bitrate/1000000||'G'
      when bitrate >=1000 and bitrate <=1000000 then bitrate/1000||'M'
      when REGEXP_LIKE(bitrate, '[^a-zA-Z]', '') then bitrate
      else
      bitrate||'K'
      end speed_decoded from circuit

      But when i scroll down, I get invalid number error. I guess I am getting this error at character data.
      Please help how can i check for non numeric data in case and retrive it.

      ORA-01722: invalid number

      Please do the needful.

      Thanks in advance,
      Preeti.
        • 1. Re: Case not working for non numeric data
          jeneesh
          Welcome to the forum..
          Please provide sample data and expected output

          Edited by: jeneesh on Oct 9, 2012 4:21 PM
          • 2. Re: Case not working for non numeric data
            Most Wanted!!!!
            SELECT bitrate,
                   CASE
                      WHEN bitrate >= 1000000
                         THEN bitrate / 1000000 || 'G'
                      WHEN bitrate >= 1000 AND bitrate <= 1000000
                         THEN bitrate / 1000 || 'M'
                      WHEN REGEXP_LIKE (bitrate, '[^a-zA-Z]', '')
                         THEN bitrate
                      ELSE bitrate || 'K'
                   END speed_decoded
              FROM (SELECT '1003' AS bitrate
                      FROM DUAL);
            regards ,
            friend
            • 3. Re: Case not working for non numeric data
              Arun Kumar Gupta
              Try this query

              select bitrate,case
              when REGEXP_LIKE(bitrate, '[^a-zA-Z]', '') then bitrate
              when bitrate >=1000000 then bitrate/1000000||'G'
              when bitrate >=1000 and bitrate <=1000000 then bitrate/1000||'M'
              else
              bitrate||'K'
              end speed_decoded from circuit


              Regards
              Arun
              • 4. Re: Case not working for non numeric data
                jeneesh
                most wanted!!!! wrote:
                SELECT bitrate,
                CASE
                WHEN bitrate >= 1000000
                THEN bitrate / 1000000 || 'G'
                WHEN bitrate >= 1000 AND bitrate <= 1000000
                THEN bitrate / 1000 || 'M'
                WHEN REGEXP_LIKE (bitrate, '[^a-zA-Z]', '')
                THEN bitrate
                ELSE bitrate || 'K'
                END speed_decoded
                FROM (SELECT '1003' AS bitrate
                FROM DUAL);
                regards ,
                friend
                SQL> SELECT bitrate,
                  2         CASE
                  3            WHEN bitrate >= 1000000
                  4               THEN bitrate / 1000000 || 'G'
                  5            WHEN bitrate >= 1000 AND bitrate <= 1000000
                  6               THEN bitrate / 1000 || 'M'
                  7            WHEN REGEXP_LIKE (bitrate, '[^a-zA-Z]', '')
                  8               THEN bitrate
                  9            ELSE bitrate || 'K'
                 10         END speed_decoded
                 11    FROM (SELECT 'XXX' AS bitrate
                 12            FROM DUAL);
                SELECT bitrate,
                       *
                ERROR at line 1:
                ORA-01722: invalid number
                • 5. Re: Case not working for non numeric data
                  johnblr
                  In the division operation use a bracket. This should solve your issue i guess

                  Instead of

                  bitrate/1000000||'G' -- you are trying to divide bitrate with a string -- Concatenation operator has more precedence than division.

                  use

                  (bitrate/1000000)||'G'
                  • 6. Re: Case not working for non numeric data
                    jeneesh
                    John wrote:
                    Concatenation operator has more precedence than division.
                    I dont think so
                    SQL> select 1000/100||'Z' r from dual;
                    
                    R
                    ---
                    10Z
                    • 7. Re: Case not working for non numeric data
                      Ravetd
                      Hi,
                      SELECT bitrate,
                                CASE
                                   WHEN REGEXP_LIKE (bitrate, '^\d+$') And bitrate >= 1000000
                                      THEN bitrate / 1000000 || 'G'
                                   WHEN REGEXP_LIKE (bitrate, '^\d+$') And bitrate >= 1000 AND bitrate <= 1000000
                                      THEN bitrate / 1000 || 'M'
                                   WHEN  REGEXP_LIKE (bitrate, '\D') 
                                      THEN bitrate
                                   ELSE bitrate || 'K'
                                END speed_decoded
                           FROM (SELECT '103x' AS bitrate
                                   FROM DUAL)
                      regards.
                      • 8. Re: Case not working for non numeric data
                        Paul  Horth
                        964085 wrote:
                        Hi All,

                        I want to check for a column value, if its non numeric then i should retrieve data as it is else i should check for other conditions.

                        My query is:

                        select bitrate,case
                        when bitrate >=1000000 then bitrate/1000000||'G'
                        when bitrate >=1000 and bitrate <=1000000 then bitrate/1000||'M'
                        when REGEXP_LIKE(bitrate, '[^a-zA-Z]', '') then bitrate
                        else
                        bitrate||'K'
                        end speed_decoded from circuit

                        But when i scroll down, I get invalid number error. I guess I am getting this error at character data.
                        Please help how can i check for non numeric data in case and retrive it.

                        ORA-01722: invalid number

                        Please do the needful.

                        Thanks in advance,
                        Preeti.
                        The problem is that bitrate is a string and you have non-numeric data in it.
                        So when you do

                        when bitrate >=1000000 then ...

                        Oracle tries to convert whatever is in bitrate to a number: and in some cases this will fail.

                        Why have such a horrible design? bitrate should be pure numeric. Clean up your data
                        before you try and report on it.
                        • 9. Re: Case not working for non numeric data
                          Paul  Horth
                          Ravetd wrote:
                          Hi,
                          SELECT bitrate,
                          CASE
                          WHEN REGEXP_LIKE (bitrate, '^\d+$') And bitrate >= 1000000
                          THEN bitrate / 1000000 || 'G'
                          WHEN REGEXP_LIKE (bitrate, '^\d+$') And bitrate >= 1000 AND bitrate <= 1000000
                          THEN bitrate / 1000 || 'M'
                          WHEN  REGEXP_LIKE (bitrate, '\D') 
                          THEN bitrate
                          ELSE bitrate || 'K'
                          END speed_decoded
                          FROM (SELECT '103x' AS bitrate
                          FROM DUAL)
                          regards.
                          SQL is a non-procedural language. Can we guarantee the order of evaluation of
                          WHEN REGEXP_LIKE (bitrate, '^\d+$') And bitrate >= 1000000
                          ?
                          will it always evaluate the regexp_like first?
                          • 10. Re: Case not working for non numeric data
                            Kim Berg Hansen
                            An alternative method:

                            Start with defining your own version of the to_number function:
                            create or replace function to_number_null (
                               expr       in   varchar2,
                               fmt        in   varchar2 default null,
                               nlsparam   in   varchar2 default null
                            )
                               return number
                            is
                               numeric_or_value_error   exception;
                               pragma exception_init (numeric_or_value_error, -6502);
                            begin
                               if nlsparam is not null and fmt is not null
                               then
                                  return to_number (expr, fmt, nlsparam);
                               elsif fmt is not null
                               then
                                  return to_number (expr, fmt);
                               else
                                  return to_number (expr);
                               end if;
                            exception
                               when numeric_or_value_error
                               then
                                  return null;
                            end to_number_null;
                            /
                            This version catches the numeric or value error and returns a null value in case the string does not contain valid numeric data.

                            With the to_number_null function you can do something like this:
                            SQL> with circuit as (
                              2     select '123456789' bitrate from dual union all
                              3     select '123456' bitrate from dual union all
                              4     select '123' bitrate from dual union all
                              5     select '1234X' bitrate from dual
                              6  )
                              7  select bitrate
                              8       , to_number_null(bitrate) bit_as_num
                              9    from circuit
                             10  /
                            
                            BITRATE   BIT_AS_NUM
                            --------- ----------
                            123456789  123456789
                            123456        123456
                            123              123
                            1234X
                            The last value with the 'X' in it returns null.
                            Then you can do this:
                            SQL> with circuit as (
                              2     select '123456789' bitrate from dual union all
                              3     select '123456' bitrate from dual union all
                              4     select '123' bitrate from dual union all
                              5     select '1234X' bitrate from dual
                              6  )
                              7  select bitrate
                              8       , bit_as_num
                              9       , case
                             10           when bit_as_num is null   then bitrate
                             11           when bit_as_num > 1000000 then to_char(bit_as_num/1000000,'TM9')||'G'
                             12           when bit_as_num > 1000    then to_char(bit_as_num/1000   ,'TM9')||'M'
                             13           else                           to_char(bit_as_num        ,'TM9')||'K'
                             14         end speed_decoded
                             15    from (
                             16     select bitrate
                             17          , to_number_null(bitrate) bit_as_num
                             18       from circuit
                             19         )
                             20  /
                            
                            BITRATE   BIT_AS_NUM SPEED_DECODED
                            --------- ---------- ---------------
                            123456789  123456789 123,456789G
                            123456        123456 123,456M
                            123              123 123K
                            1234X                1234X
                            Depending on how much of your data is "non-valid numeric" this may or may not be more efficient than doing a lot of regexp cpu intensive functions. Exception handling is pretty efficient.
                            On the other hand this introduces a lot of switching between SQL and PL/SQL context, which could be a potential issue.

                            You will have to try the different methods and benchmark them against your data ;-)
                            • 11. Re: Case not working for non numeric data
                              Sven W.
                              Paul Horth wrote:
                              SQL is a non-procedural language. Can we guarantee the order of evaluation of
                              WHEN REGEXP_LIKE (bitrate, '^\d+$') And bitrate >= 1000000
                              ?
                              will it always evaluate the regexp_like first?
                              In a sense yes it is possible. But not for the AND comparision, but for the several case expression themselves.

                              From the docs: http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

                              In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, Oracle returns else_expr. Otherwise, Oracle returns null.
                              Oracle Database uses short-circuit evaluation. That is, for a simple CASE expression, the database evaluates each comparison_expr value only before comparing it to expr, rather than evaluating all comparison_expr values before comparing any of them with expr. Consequently, Oracle never evaluates a comparison_expr if a previous comparison_expr is equal to expr. For a searched CASE expression, the database evaluates each condition to determine whether it is true, and never evaluates a condition if the previous condition was true.

                              Therefore the solution posted by arun will probably work:
                              select bitrate,
                                case
                                   when REGEXP_LIKE(bitrate, '[^a-zA-Z]', '') then bitrate
                                   when bitrate >=1000000 then bitrate/1000000||'G'
                                   when bitrate >=1000 and bitrate <=1000000 then bitrate/1000||'M'
                                else bitrate||'K'
                              end speed_decoded from circuit
                              Edited by: Sven W. on Oct 9, 2012 2:33 PM
                              • 12. Re: Case not working for non numeric data
                                967088
                                Thank u so much Ravetd . Your query is working :)

                                Thanks,
                                Preeti.

                                Edited by: 964085 on Oct 9, 2012 5:40 AM
                                • 13. Re: Case not working for non numeric data
                                  967088
                                  Thank u so much Ravetd . Your query is working :)

                                  Thanks,
                                  Preeti.
                                  • 14. Re: Case not working for non numeric data
                                    967088
                                    Thank u all for your quick responses.

                                    Thanks,
                                    Preeti.
                                    1 2 Previous Next