All Forums Analytics
05 Jun 2009
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-------TERADATARow1: 0.546476429------------------1Row2: 0.095457194------------------0Row3: 0.254218001------------------1Row4: 0.054972349------------------0Row5: 0.978196729------------------1 Thank you sooo much in advance, hoping that I can get the same result as the oracle's!

Jim Chapman 449 posts Joined 09/04
05 Jun 2009

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

08 Jun 2009

woah!!! thank you sooo much!

JPH 2 posts Joined 11/12
03 Dec 2012

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!

ulrich 816 posts Joined 09/09
03 Dec 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Malvi Jaggi 4 posts Joined 09/11
19 Dec 2012

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

You must sign in to leave a comment.