Random values


Random values


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

random(0,1) as random_nbr


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!

Re: Random values

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


Re: Random values

woah!!! thank you sooo much!

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.


Re: Random values


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