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.
Thank you in advance,
Marcin
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.
Please check if this can be used in your requirement.
Regards,
Ravi
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
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;
Add a comment