Remainder function

Moderator: NorbertKrupa

Post Reply
billykopecki
Beginner
Beginner
Posts: 42
Joined: Thu Apr 19, 2012 9:03 pm

Remainder function

Post by billykopecki » Wed Oct 09, 2013 4:31 pm

Hello,

I'm converting a bunch of Oracle SQL which uses a function called remainder(). Is there something similar in Vertica? If I have to write my own do you think I can use the mod function? I'm worried by this statement in the Oracle doc:
The MOD function is similar to REMAINDER except that it uses FLOOR in its formula, whereas REMAINDER uses ROUND.
How does MOD work in Vertica?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Remainder function

Post by JimKnicely » Wed Oct 09, 2013 8:49 pm

Hi billykopecki,

Since the Oracle REMAINDER (m, n) function result is calculated as:

m - (n * X) where X is the integer nearest m / n

In Vertica, you can create two REMAINDER functions like this:

Code: Select all

dbadmin=> CREATE OR REPLACE FUNCTION remainder (m INT, n INT) RETURN FLOAT
dbadmin-> AS
dbadmin-> BEGIN
dbadmin-> RETURN m - (n * ROUND(m / n));
dbadmin-> END;
CREATE FUNCTION
dbadmin=>
dbadmin=> CREATE OR REPLACE FUNCTION remainder (m FLOAT, n FLOAT) RETURN FLOAT
dbadmin-> AS
dbadmin-> BEGIN
dbadmin-> RETURN m - (n * ROUND(m / n));
dbadmin-> END;
CREATE FUNCTION
Examples:

Code: Select all

dbadmin=> SELECT remainder(15, 6), remainder(15,5), remainder(15, 4), remainder(11.6, 2), remainder(11.6, 2.1), remainder(-15, 4);
 remainder | remainder | remainder | remainder | remainder | remainder
-----------+-----------+-----------+-----------+-----------+-----------
        -3 |         0 |        -1 |      -0.4 |        -1 |         1
(1 row)

dbadmin=> SELECT remainder(5, 2), remainder(5, 5), remainder(2, 5), remainder(-2, 5), remainder(5, -1), remainder(5, 0.5), remainder(5, 1.2);
 remainder | remainder | remainder | remainder | remainder | remainder | remainder
-----------+-----------+-----------+-----------+-----------+-----------+-----------
        -1 |         0 |         2 |        -2 |         0 |         0 |       0.2
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

billykopecki
Beginner
Beginner
Posts: 42
Joined: Thu Apr 19, 2012 9:03 pm

Re: Remainder function

Post by billykopecki » Thu Oct 10, 2013 2:40 am

Hey, thanks, Jim! That's awesome :D

Post Reply

Return to “Vertica SQL Functions”