Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Does Oracle have this kind of function?

    Does anyone know if Oracle has a function that works like the Excel "ceiling" function, which returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Try the ROUND(col1,2) function.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    I don't think that will work.

    I need a qty = 545, and significance = 300, and I need it to result in 600, which is 545 rounded to the nearest multiple of significance 300.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Then: NO, Oracle does not have equivalent function. But you could code one!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    SQL> var x number
    SQL> var y number
    SQL> exec :x := 545;

    PL/SQL procedure successfully completed.

    SQL> exec :y := 300;

    PL/SQL procedure successfully completed.

    SQL> select ceil(:x / :y) * :y from dual;

    CEIL(:X/:Y)*:Y
    --------------
    600

    SQL> exec :x := 601;

    PL/SQL procedure successfully completed.

    SQL> select ceil(:x / :y) * :y from dual;

    CEIL(:X/:Y)*:Y
    --------------
    900

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    HrabiaGrzegorz: I suspected it would be as simple as you posted -- Good job!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    HrabiaGrzegorz, clever indeed.

  8. #8
    Join Date
    Sep 2005
    Posts
    220
    Thanks. That works great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •