0

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.

1
  • Oracle does allow you to define your own functions. Feb 13 '18 at 19:34
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.