Ensure percentages are between 0 and 1, inclusive (using a single function)

oracleoracle-12cselect

I have percentages in a condition table:

create table condition (percent_decimal number(3,2));
insert into condition values (-0.01);
insert into condition values (0.1);
insert into condition values (1);
insert into condition values (1.1);
commit;

PERCENT_DECIMAL
---------------
          -0.01
             .1
              1
            1.1

I want to select the values, but modify them to present them as percentages between 0 and 1 (inclusive):

  • Convert -0.01 to 0
  • Leave .1 as is
  • Leave 1 as is
  • Convert 1.1 to 1

I can successfully do this using the greatest and least functions:

select
    percent_decimal,
    least(1,greatest(0,percent_decimal)) as percent_modified
from
    condition

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

However, I'm wondering if there is a more succinct way of doing this–with a single function.

I'm often pleasantly surprised by the clever techniques that DBA-SE members come up with. When I think I've come up with the simplest way to do something, someone comes up with something even simpler.

Best Answer

Ok, this can't be a serious question, reminds me of https://codegolf.stackexchange.com/

I can do better, no function at all.

select
  percent_decimal,
  case
    when percent_decimal <= 0 then 0
    when percent_decimal >= 1 then 1
    else percent_decimal
  end as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

But we need a function. Finally Oracle 12.2 allows long (128 bytes) identifiers, so we can give more meaningful names:

with function 
  "Convert -0.01 to 0, Leave .1 as is, Leave 1 as is, Convert 1.1 to 1" (p number)
return number
as
begin
  if (p = -0.01) then return 0; end if;
  if (p = 1.1) then return 1; end if;
  return p;
end;
select
  percent_decimal,
  "Convert -0.01 to 0, Leave .1 as is, Leave 1 as is, Convert 1.1 to 1"(percent_decimal) 
    as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

I also happen to have a super secret function that coincidentally does exactly what you need:

create or replace function "." wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
97 aa
30RI5qP/Li6VhppxgO2AXQw62mYwg1xfrZ6pfI6iO8cYr/L9s3tguDonZ52omRm1G6sfUCen
xkcEU/4jCxrp7Ii4VNrCrVY6cDr0UNz1Id5vZ5p7e0S0F+AEd55sz9JyGokPaBbDJHFVdW+A
IAHg6Xpxmglu3BtbDtBmCA==

/

select
  percent_decimal,
  "."(percent_decimal) as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

Must use a built-in function? No problem.

select
  percent_decimal,
  round(percent_decimal/1.05, 1) as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1
Related Question