Random values

Analytics
Enthusiast

Random values

Hi,

I hope somebody can help me with my task. We are currently in the part of transitioning from oracle to teradata, and the problem that I currently have and which I really cannot identify is on how to produce random values in teradata the same way it was produced in oracle. Confusing but I have copied the results that I wanted to produce below:

ORACLE code:
dbms_random.value(0,1) as random_nbr

TERADATA code:
random(0,1) as random_nbr

RESULTS:

--------ORACLE--------vs-------TERADATA
Row1: 0.546476429------------------1
Row2: 0.095457194------------------0
Row3: 0.254218001------------------1
Row4: 0.054972349------------------0
Row5: 0.978196729------------------1

Thank you sooo much in advance, hoping that I can get the same result as the oracle's!
5 REPLIES
Enthusiast

Re: Random values

select cast(random(0,999999999) as float)/1000000000 (format '9.999999999') as random_nbr;

random_nbr
-----------
0.674948197
Enthusiast

Re: Random values

woah!!! thank you sooo much!
JPH
Fan

Re: Random values

I'm also thinking about transitioning from Oracle to Teradata, and one of the issues I'm worried about is the random number function in Teradata...mainly that it calculates a new value everytime it is run, whereas in Oracle it can be the same on repeated runs.

Is there any way to upload or transfer the Oracle random number function into Teradata?

Thanks for the help!

Senior Supporter

Re: Random values

I guess you are refering to dbms_random?

I have some doubts that you can migrate this function to TD. Main issue is the parallel data distribution and parallel processing.

You clearly don't want to have a central maintained seed. 

So alternative would be to initiallize this per vproc, which would still give you the problem that in case of sync scans the first row per SQL could be different even if you submit the same SQL twice.

What might work is to implement a pseudo random generator as UDF and you use the row input as seed - rowid would be best but can't be used any more. So you need to do it more complidated.

This would give you per row a different output number -  still multiset tables would be an issue as same rows will get same output.

Enthusiast

Re: Random values

Hi,

I have a never used random function at my work. I want to get some examples how  we use random function in practical situation?

Thanks