Former Member

# Do you have any idea how to create and use RAND() function in HANA?

Hi,

I'm new in SAP HANA and I try to select random rows from result set, in a way similar to:

SELECT * FROM table WHERE RAND() > 0.5;

Could someone tell mi how can I create RAND() function (eg. how can I return calculation result, not an algorithm) from custom procedure

and then how can I use it in query.

Marcin

10|10000 characters needed characters exceeded

### Related questions

• Posted on Sep 20, 2012 at 05:20 PM

Hi Marcin,

I don't think there is a RAND() function defined in HANA yet, but something similar to what I had implemented in the past, the random number can be generated from the microsecond value of the time function.

You can try using substring(now(), 21,3) which give the 3 digit (or even last or first 2 digits) microsecond value of the time, which can be used to generate the random number.

Regards,

Ravi

10|10000 characters needed characters exceeded
• Former Member Ravindra Channe

Hi Ravi,

Yes, I have thought about combining microseconds with SIN() numbers (after reading your post), but I already have written my RAND() procedure. If you are interested, here is the source code (maybe it helps someone). It is based on George Marsaglia's algorithm (http://www.codeproject.com/Articles/25172/Simple-Random-Number-Generation) and requires a table (named RAND_PARAMS) to store generator values:

-- prerequisites:

CREATE TABLE RAND_PARAMS (M_Z BIGINT, M_W BIGINT);

INSERT INTO RAND_PARAMS VALUES(362436069,521288629); -- generator seeds

CREATE PROCEDURE RAND(OUT o DOUBLE) LANGUAGE SQLSCRIPT AS

m_z BIGINT;

m_w BIGINT;

BEGIN

SELECT M_Z, M_W INTO m_z, m_w FROM RAND_PARAMS;

m_z := 36969 * BITAND(:m_z,65535) + BITAND(:m_z,4294901760)/65536; -- m_z = 36969 * (m_z & 65535) + (m_z >> 16);

m_w := 18000 * BITAND(:m_w,65535) + BITAND(:m_w,4294901760)/65536; -- m_w = 18000 * (m_w & 65535) + (m_w >> 16);

o := :m_z *65536 + :m_w; -- return (m_z << 16) + m_w;

o := BITAND(:o, 4294967295)/4294967296.0; -- TO_INT(o)/MAX_RAND -> let's scale to <0,1) range

UPDATE RAND_PARAMS SET M_Z = BITAND(:m_z, 4294967295), M_W = BITAND(:m_w, 4294967295); -- let's preserve 32 bits only

END;

thanks for your interest; best regards

Marcin

• Former Member
Posted on Sep 25, 2012 at 10:09 PM

Hi Marcin,

You can perhaps try to create a simple produre that returns the value with SELECT query using current system timestamp on miliseconds fraction.

For example:

3-digits number:

SELECT TO_NUMBER(TO_CHAR(NOW(),'FF3')) FROM DUMMY

Regards,

Ferry

10|10000 characters needed characters exceeded
• Former Member
Posted on Sep 26, 2012 at 05:48 AM

Hi everyone,

I've been told there is undocumented RAND() function (from rev. 32). I've tried it and it really works:

select RAND() from dummy;

Thanks everyone for help,

regards

Marcin

10|10000 characters needed characters exceeded
• Former Member Ravindra Channe

regards

Marcin

• Posted on Oct 08, 2012 at 03:20 AM

This is from a while ago, but just to report that (at least in rev36+), I have RAND() as a valid function.

I didn't find it in the documentation, but testing a little bit, it apparently returns a (float?) number from 0 to 1.

Using simple multipliers & sums, it's possible to achieve a regular random function.

For example, a function that always returns an integer between A and B:

SELECT TO_INT('A' + ('B'-'A')*RAND()) FROM DUMMY;

10|10000 characters needed characters exceeded
• Former Member
Posted on Jan 21, 2014 at 10:04 AM

Hi,

in SP7 RAND() function included into official documentation:

Description

Returns a pseudo-random value in the range of [0, 1.0). Its return value type is DOUBLE.

Example

SELECT RAND() FROM DUMMY;

RAND

3.907985046680551e-14

Denis.