Forum Stats

  • 3,770,165 Users
  • 2,253,079 Discussions
  • 7,875,353 Comments

Discussions

Adding string to number in Case Statement

User_QDHXF
User_QDHXF Member Posts: 20 Green Ribbon

Hello,

Create Table T1_Temp(N1 number,N2 number);

insert into T1_TEMP values (162894684325,null);

insert into T1_TEMP values (-5635465675,null);

insert into T1_TEMP values (152623721,null);

insert into T1_TEMP values (-52645612,null);

insert into T1_TEMP values (10012.54,null);

insert into T1_TEMP values (-52645612,null);

insert into T1_TEMP values (8962,null);

insert into T1_TEMP values (-7854.2,null);

Oracle DB Version: Oracle 19c (Enterprise Edition Release 19.0.0.0.0)

I have a sample number format like:


I would like to write a case statement to convert these numbers into:

Essentially, the clause for the conversion is:


I am getting invalid character error when I try to concatenate a '$' and a 'B' for the number in a case statement. Any help is greatly appreciated.


Thanks

Tagged:

Best Answers

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Oct 11, 2021 7:41PM Accepted Answer

    N2 cannot be a number, it has to be a string.

    You can create FormatSize as a function, rather than an inline function. Assumptions : your NLS Currency symbol is $, thousand separator is "," and decimal separator is "."

    FML999G999D0 => FM = Fill Mode, L = Currency, G = Thousand Separator, D = Decimal Separator, 9 = include digit if exists here, 0 = Include digit if exists here, otherwise 0.

    I loop to determine magnitude, you could do it with logs instead, but might be slower.

    with
      function FormatSize(pValue number) return varchar2 is
        POSTFIX constant varchar2(4) := 'KMBT'; -- Kilo, Millions, Billions, Trillions  
        vMaxMag integer := length(POSTFIX);
        vValue number := pValue;
        vMagnitude number := 0;
        vDivisor integer := 1000;    
      begin
        if abs(pValue) >= 10000 then
          while (abs(vValue) >= vDivisor) and vMagnitude < vMaxMag
          loop
            vValue := vValue / vDivisor;
            vMagnitude := vMagnitude + 1;
          end loop;
        end if;
        return to_char(round(vValue, 1), 'FML999G999D0') || case when vMagnitude > 0 then substr(POSTFIX, vMagnitude, 1) end;
      end;
    select N1, formatsize(N1) N2
    from T1_Temp
    
    
            N1   |  N2
    -------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    162894684325	$162.9B
     -5635465675	-$5.6B
       152623721	$152.6M
       -52645612	-$52.6M
        10012.54	$10.0K
       -52645612	-$52.6M
            8962	$8,962.0
         -7854.2	-$7,854.2
    
    
    
    8 rows selected.
    
  • mathguy
    mathguy Member Posts: 10,164 Blue Diamond
    edited Oct 11, 2021 10:31PM Accepted Answer

    It's interesting that for numbers between 1,000 and 9,999 you want to show the numbers as they are, rather than transform that with K. So for example 3,230 is shown exactly as 3,230 and not as 3.2K. Are you 100% sure about that? Especially since you are not going all the way on that idea (3,230,000 is shown as 3.2M, not as 3,230K). This is the kind of inconsistent formatting that as a business user I would find very distracting (and annoying).

    Anyway - for the rest of this Reply I will assume you know what you are doing, and you do indeed need to show numbers exactly as you showed us.

    In the insert statements you included the same value twice (-52645612) and you didn't include one of the values from your pictures (-529556). This is why we ask posters like you to run all the create table and insert statements themselves to make sure they are correct - apparently in your case they aren't.

    Also regarding your missing value, -529556, in the "required output" you show it as -$52.9K. There are two glaring mistakes, I hope you agree (and accept that they are mistakes rather than your real requirement; if they are your true requirement, I will need to give up). First, -529556 is about -529K, not 52.9K. You may represent numbers in any format you want, but 52.9K means about 52,900 in any normal-brained system, not 529,000. And second, on other rows you show that you are rounding the values, not truncating them. 529550 rounds to 530000, not to 529550. What did you do there - round "by hand"? Do this in any spreadsheet or other computer software, you won't get that result if you round - only if you truncate (but in other rows, you didn't truncate, you rounded).

    Now to the real problem.

    It is a very poor practice to store such calculated values in the table. The better (and more common) practice is to create a view and select from the view when you need the specific presentation of the numbers, or to create the column as a virtual column. No data is saved in it - only the formula, and the value is always calculated on the fly when a SQL statement references that column. This is what I show below. (Read the comments after the code too.) The solution I am proposing is the definition of column n2 below. The keyword as indicates that the column is a virtual column - calculated by the expression given in parentheses after the keyword as.

    create table t1_temp (
      n1 number,
      n2 as (case when abs(n1) < 1e4 then to_char(n1      , 'fm$9,990.0')
                  when abs(n1) < 1e6 then to_char(n1 / 1e3, 'fm$990.0') || 'K'
                  when abs(n1) < 1e9 then to_char(n1 / 1e6, 'fm$990.0') || 'M'
                  else                    to_char(n1 / 1e9, 'fm$999,999,990.0') || 'B' end)
    );
    
    insert into t1_temp(n1) values (162894684325);
    insert into t1_temp(n1) values (-5635465675);
    insert into t1_temp(n1) values (152623721);
    insert into t1_temp(n1) values (-52645612);
    insert into t1_temp(n1) values (10012.54);
    insert into t1_temp(n1) values (-529556);
    insert into t1_temp(n1) values (8962);
    insert into t1_temp(n1) values (-7854.2);
    insert into t1_temp(n1) values (0);
    commit;
    
    select * from t1_temp;
    
            N1 N2              
    ---------- ----------------
    1.6289E+11 $162.9B         
    -5.635E+09 -$5.6B          
     152623721 $152.6M         
     -52645612 -$52.6M         
      10012.54 $10.0K          
       -529556 -$529.6K        
          8962 $8,962.0        
       -7854.2 -$7,854.2       
             0 $0.0   
    
    


    For testing I also included the value 0 to make sure it is formatted as needed.

    Note - If you create a virtual column as I showed above, then in your insert statements with the value clause you will have to name the column(s) you insert into explicitly. If you only give a value for n1 Oracle will complain that you didn't give it enough values - and it makes no sense to give values to a calculated column. You could make the column invisible and still use insert with the value clause without naming the specific columns (which is a very poor practice anyway, you shouldn't do that), but then a select * from the table will not return column n2 - you would have to name the columns explicitly in every select that needs to access n2.

    In the format models you can use the elements L for currency, G for group separator (thousands separator), and D for decimal separator, as Paulzip suggested. You can also modify the definition of n2 slightly, as needed, to allow for T also, for "trillions". You will also note that I used scientific notation - I don't want to have to count zeros to figure out what 1000000000 is. Much easier if I write that as 1e9 (assuming I counted right).

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    Accepted Answer

    Hi, @User_QDHXF

    Storing values that can be computed from other columns in the same row is usually not a good idea. If you do store n2, be careful that it gets updated automatically whenever n1 gets updated. You may want to make n2 a virtual column.

    I like Paulzip's idea of a user-defined function. Chances are very good that you'll need to do the same transformation in other places.

    If you want to use a CASE expression (not the same thing as a CASE statement), here's one way:

    CREATE OR REPLACE FUNCTION FormatSize (n IN NUMBER)
    RETURN VARCHAR2
    DETERMINISTIC
    IS
    BEGIN
       RETURN CASE
    	     WHEN ABS (n) >= 1E9 THEN TO_CHAR ( n / 1E9
    					      , 'FML999,999,999,999.0'
    		 	   	  	      ) || 'B'
    	     WHEN ABS (n) >= 1E6 THEN TO_CHAR ( n / 1E6
    					      , 'FML999.0'
    		 	   	  	      ) || 'M'
    	     WHEN ABS (n) >= 1E4 THEN TO_CHAR ( n / 1E3
    					      , 'FML999.0'
    		 	   	  	      ) || 'K'
    	     WHEN n IS NOT NULL  THEN TO_CHAR (n, 'FML9,999.0')
    	   END;
    END FormatSize;
    

    If you really, really, really must do this in SQL, the exact same CASE expression will work in SQL.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,493 Red Diamond
    Accepted Answer

    As Mathguy says, I'm not sure why you'd want values less than 10,000 but over 999 to be displayed without the "K" value.

    SQL> ed
    Wrote file afiedt.buf
    
      1  with t(n) as (
      2    select 162894684325 from dual union all
      3    select -5635465675 from dual union all
      4    select 152623721 from dual union all
      5    select -52645612 from dual union all
      6    select 10012.54 from dual union all
      7    select -52645612 from dual union all
      8    select 8962 from dual union all
      9    select -7854.2 from dual union all
     10    select 123 from dual union all
     11    select -234 from dual
     12    )
     13  select t.n
     14        ,case when abs(t.n) >= 10000 then
     15           to_char(round(t.n/power(1000,ceil(length(to_char(abs(round(t.n))))/3)-1),1),'FML999G999D0')||substr('KMBT',ceil(length(to_char(abs(round(t.n))))/3)-1,1)
     16         else
     17           to_char(t.n,'FML999G999D0')
     18         end as val
     19        ,to_char(round(t.n/power(1000,ceil(length(to_char(abs(round(t.n))))/3)-1),1),'FML999G999D0')||substr(' KMBT',ceil(length(to_char(abs(round(t.n))))/3),1) as alt_val
     20* from   t
    SQL> /
    
    
                  N VAL                   ALT_VAL
    --------------- --------------------- ---------------------
       162894684325 $162.9B               $162.9B
        -5635465675 -$5.6B                -$5.6B
          152623721 $152.6M               $152.6M
          -52645612 -$52.6M               -$52.6M
              10013 $10.0K                $10.0K
          -52645612 -$52.6M               -$52.6M
               8962 $8,962.0              $9.0K
              -7854 -$7,854.2             -$7.9K
                123 $123.0                $123.0
               -234 -$234.0               -$234.0
    
    
    10 rows selected.
    

    in this case the "val" is what you asked for and the "alt_val" is treating the thousands values properly with the "K" (which also avoids the need for the case expression)

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi, @User_QDHXF

    I am getting invalid character error when I try to concatenate a '$' and a 'B' for the number in a case statement. Any help is greatly appreciated.

    Strings can have characters like '$' and 'B', NUMBERs can't. If you want to store things like '$162.9B' in column n2, then make n2 a string, such as VARCHAR2 (12).

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Oct 11, 2021 7:41PM Accepted Answer

    N2 cannot be a number, it has to be a string.

    You can create FormatSize as a function, rather than an inline function. Assumptions : your NLS Currency symbol is $, thousand separator is "," and decimal separator is "."

    FML999G999D0 => FM = Fill Mode, L = Currency, G = Thousand Separator, D = Decimal Separator, 9 = include digit if exists here, 0 = Include digit if exists here, otherwise 0.

    I loop to determine magnitude, you could do it with logs instead, but might be slower.

    with
      function FormatSize(pValue number) return varchar2 is
        POSTFIX constant varchar2(4) := 'KMBT'; -- Kilo, Millions, Billions, Trillions  
        vMaxMag integer := length(POSTFIX);
        vValue number := pValue;
        vMagnitude number := 0;
        vDivisor integer := 1000;    
      begin
        if abs(pValue) >= 10000 then
          while (abs(vValue) >= vDivisor) and vMagnitude < vMaxMag
          loop
            vValue := vValue / vDivisor;
            vMagnitude := vMagnitude + 1;
          end loop;
        end if;
        return to_char(round(vValue, 1), 'FML999G999D0') || case when vMagnitude > 0 then substr(POSTFIX, vMagnitude, 1) end;
      end;
    select N1, formatsize(N1) N2
    from T1_Temp
    
    
            N1   |  N2
    -------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    162894684325	$162.9B
     -5635465675	-$5.6B
       152623721	$152.6M
       -52645612	-$52.6M
        10012.54	$10.0K
       -52645612	-$52.6M
            8962	$8,962.0
         -7854.2	-$7,854.2
    
    
    
    8 rows selected.
    
  • mathguy
    mathguy Member Posts: 10,164 Blue Diamond
    edited Oct 11, 2021 10:31PM Accepted Answer

    It's interesting that for numbers between 1,000 and 9,999 you want to show the numbers as they are, rather than transform that with K. So for example 3,230 is shown exactly as 3,230 and not as 3.2K. Are you 100% sure about that? Especially since you are not going all the way on that idea (3,230,000 is shown as 3.2M, not as 3,230K). This is the kind of inconsistent formatting that as a business user I would find very distracting (and annoying).

    Anyway - for the rest of this Reply I will assume you know what you are doing, and you do indeed need to show numbers exactly as you showed us.

    In the insert statements you included the same value twice (-52645612) and you didn't include one of the values from your pictures (-529556). This is why we ask posters like you to run all the create table and insert statements themselves to make sure they are correct - apparently in your case they aren't.

    Also regarding your missing value, -529556, in the "required output" you show it as -$52.9K. There are two glaring mistakes, I hope you agree (and accept that they are mistakes rather than your real requirement; if they are your true requirement, I will need to give up). First, -529556 is about -529K, not 52.9K. You may represent numbers in any format you want, but 52.9K means about 52,900 in any normal-brained system, not 529,000. And second, on other rows you show that you are rounding the values, not truncating them. 529550 rounds to 530000, not to 529550. What did you do there - round "by hand"? Do this in any spreadsheet or other computer software, you won't get that result if you round - only if you truncate (but in other rows, you didn't truncate, you rounded).

    Now to the real problem.

    It is a very poor practice to store such calculated values in the table. The better (and more common) practice is to create a view and select from the view when you need the specific presentation of the numbers, or to create the column as a virtual column. No data is saved in it - only the formula, and the value is always calculated on the fly when a SQL statement references that column. This is what I show below. (Read the comments after the code too.) The solution I am proposing is the definition of column n2 below. The keyword as indicates that the column is a virtual column - calculated by the expression given in parentheses after the keyword as.

    create table t1_temp (
      n1 number,
      n2 as (case when abs(n1) < 1e4 then to_char(n1      , 'fm$9,990.0')
                  when abs(n1) < 1e6 then to_char(n1 / 1e3, 'fm$990.0') || 'K'
                  when abs(n1) < 1e9 then to_char(n1 / 1e6, 'fm$990.0') || 'M'
                  else                    to_char(n1 / 1e9, 'fm$999,999,990.0') || 'B' end)
    );
    
    insert into t1_temp(n1) values (162894684325);
    insert into t1_temp(n1) values (-5635465675);
    insert into t1_temp(n1) values (152623721);
    insert into t1_temp(n1) values (-52645612);
    insert into t1_temp(n1) values (10012.54);
    insert into t1_temp(n1) values (-529556);
    insert into t1_temp(n1) values (8962);
    insert into t1_temp(n1) values (-7854.2);
    insert into t1_temp(n1) values (0);
    commit;
    
    select * from t1_temp;
    
            N1 N2              
    ---------- ----------------
    1.6289E+11 $162.9B         
    -5.635E+09 -$5.6B          
     152623721 $152.6M         
     -52645612 -$52.6M         
      10012.54 $10.0K          
       -529556 -$529.6K        
          8962 $8,962.0        
       -7854.2 -$7,854.2       
             0 $0.0   
    
    


    For testing I also included the value 0 to make sure it is formatted as needed.

    Note - If you create a virtual column as I showed above, then in your insert statements with the value clause you will have to name the column(s) you insert into explicitly. If you only give a value for n1 Oracle will complain that you didn't give it enough values - and it makes no sense to give values to a calculated column. You could make the column invisible and still use insert with the value clause without naming the specific columns (which is a very poor practice anyway, you shouldn't do that), but then a select * from the table will not return column n2 - you would have to name the columns explicitly in every select that needs to access n2.

    In the format models you can use the elements L for currency, G for group separator (thousands separator), and D for decimal separator, as Paulzip suggested. You can also modify the definition of n2 slightly, as needed, to allow for T also, for "trillions". You will also note that I used scientific notation - I don't want to have to count zeros to figure out what 1000000000 is. Much easier if I write that as 1e9 (assuming I counted right).

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    Accepted Answer

    Hi, @User_QDHXF

    Storing values that can be computed from other columns in the same row is usually not a good idea. If you do store n2, be careful that it gets updated automatically whenever n1 gets updated. You may want to make n2 a virtual column.

    I like Paulzip's idea of a user-defined function. Chances are very good that you'll need to do the same transformation in other places.

    If you want to use a CASE expression (not the same thing as a CASE statement), here's one way:

    CREATE OR REPLACE FUNCTION FormatSize (n IN NUMBER)
    RETURN VARCHAR2
    DETERMINISTIC
    IS
    BEGIN
       RETURN CASE
    	     WHEN ABS (n) >= 1E9 THEN TO_CHAR ( n / 1E9
    					      , 'FML999,999,999,999.0'
    		 	   	  	      ) || 'B'
    	     WHEN ABS (n) >= 1E6 THEN TO_CHAR ( n / 1E6
    					      , 'FML999.0'
    		 	   	  	      ) || 'M'
    	     WHEN ABS (n) >= 1E4 THEN TO_CHAR ( n / 1E3
    					      , 'FML999.0'
    		 	   	  	      ) || 'K'
    	     WHEN n IS NOT NULL  THEN TO_CHAR (n, 'FML9,999.0')
    	   END;
    END FormatSize;
    

    If you really, really, really must do this in SQL, the exact same CASE expression will work in SQL.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,493 Red Diamond
    Accepted Answer

    As Mathguy says, I'm not sure why you'd want values less than 10,000 but over 999 to be displayed without the "K" value.

    SQL> ed
    Wrote file afiedt.buf
    
      1  with t(n) as (
      2    select 162894684325 from dual union all
      3    select -5635465675 from dual union all
      4    select 152623721 from dual union all
      5    select -52645612 from dual union all
      6    select 10012.54 from dual union all
      7    select -52645612 from dual union all
      8    select 8962 from dual union all
      9    select -7854.2 from dual union all
     10    select 123 from dual union all
     11    select -234 from dual
     12    )
     13  select t.n
     14        ,case when abs(t.n) >= 10000 then
     15           to_char(round(t.n/power(1000,ceil(length(to_char(abs(round(t.n))))/3)-1),1),'FML999G999D0')||substr('KMBT',ceil(length(to_char(abs(round(t.n))))/3)-1,1)
     16         else
     17           to_char(t.n,'FML999G999D0')
     18         end as val
     19        ,to_char(round(t.n/power(1000,ceil(length(to_char(abs(round(t.n))))/3)-1),1),'FML999G999D0')||substr(' KMBT',ceil(length(to_char(abs(round(t.n))))/3),1) as alt_val
     20* from   t
    SQL> /
    
    
                  N VAL                   ALT_VAL
    --------------- --------------------- ---------------------
       162894684325 $162.9B               $162.9B
        -5635465675 -$5.6B                -$5.6B
          152623721 $152.6M               $152.6M
          -52645612 -$52.6M               -$52.6M
              10013 $10.0K                $10.0K
          -52645612 -$52.6M               -$52.6M
               8962 $8,962.0              $9.0K
              -7854 -$7,854.2             -$7.9K
                123 $123.0                $123.0
               -234 -$234.0               -$234.0
    
    
    10 rows selected.
    

    in this case the "val" is what you asked for and the "alt_val" is treating the thousands values properly with the "K" (which also avoids the need for the case expression)

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    edited Oct 12, 2021 11:06AM

    Below is incorrect - I missed n can be a fraction which bas caught by BluShadow. See my corrected solution in reply to BluShadow.

    with t(n)
      as (
          select 162894684325 from dual union all
          select -5635465675 from dual union all
          select 152623721 from dual union all
          select -52645612 from dual union all
          select 10012.54 from dual union all
          select -52645612 from dual union all
          select 8962 from dual union all
          select -7854.2 from dual union all
          select 123 from dual union all
          select -234 from dual
         )
    select  n,
            to_char(round(n / power(10,(ceil(length(abs(n)) / 3) - 1) * 3),1),'L999G999D0') ||
            case ceil(length(abs(n)) / 3) - 1
              when 1 then 'K'
              when 2 then 'M'
              when 3 then 'B'
              when 4 then 'T' -- trillion - keep adding if larger numbers are possible
            end val
      from  t
    /
    
                N VAL
    ------------- ----------------------
     162894684325               $162.9B
      -5635465675                -$5.6B
        152623721               $152.6M
        -52645612               -$52.6M
            10013                  $.0M
        -52645612               -$52.6M
             8962                 $9.0K
            -7854                -$7.9K
              123               $123.0
             -234              -$234.0
    
    10 rows selected.
    
    SQL>
    

    SY.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,493 Red Diamond
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond

    @BluShadow 10012.54          2                  $.0M

    Good catch. I missed N can be a fraction. All we need is changing N to TRUNC(N):

    with t(n)
      as (
          select 162894684325 from dual union all
          select -5635465675 from dual union all
          select 152623721 from dual union all
          select -52645612 from dual union all
          select 10012.54 from dual union all
          select -52645612 from dual union all
          select 8962 from dual union all
          select -7854.2 from dual union all
          select 123 from dual union all
          select -234 from dual
         )
    select  n,
            to_char(round(n / power(10,(ceil(length(abs(trunc(n))) / 3) - 1) * 3),1),'L999G999D0') ||
            case ceil(length(abs(trunc(n))) / 3) - 1
              when 1 then 'K'
              when 2 then 'M'
              when 3 then 'B'
              when 4 then 'T' -- trillion - keep adding if larger numbers are possible
            end val
      from  t
    /
    
                  N VAL
    --------------- ---------------------
       162894684325               $162.9B
        -5635465675                -$5.6B
          152623721               $152.6M
          -52645612               -$52.6M
           10012.54                $10.0K
          -52645612               -$52.6M
               8962                 $9.0K
            -7854.2                -$7.9K
                123               $123.0
               -234              -$234.0
    
    
    10 rows selected.
    
    
    SQL>
    
    

    SY.

    BluShadow
  • User_QDHXF
    User_QDHXF Member Posts: 20 Green Ribbon

    @mathguy, @BluShadow,@Solomon Yakobson,@Frank Kulash,@Paulzip Thank you for your response. Much appreciate the inputs.

    @mathguy , I hear you on why the number formatting is so weird. Unfortunately, that's how the business wants and I don't have much say on those requirements. I also noted the incorrect numbers on insert statement. I will pay more attention towards it.

  • mathguy
    mathguy Member Posts: 10,164 Blue Diamond

    @Solomon Yakobson

    Your solution doesn't give the required result for inputs between 1,000 and 10,000. Something like 7,923 should be shown exactly like that (7,923) in the output, not as 7.9K. The OP showed that in the original post, and I commented on it in my reply.

    Other than that, assuming that wasn't required, the simpler way to write the case statement - instead of looking at length - would be to use log(...,1e3)- with special handling for 0. That was my first thought, till I saw the special requirement for numbers between 1,000 and 10,000.