Hi Friends - In TSQL RAND function returns a decimal value between 0 and 1 and for a given value the result obtained is always same.
I Mean SELECT RAND(9) would always return 0.713741056982989 [for example].
Now, I am finding it difficult to get same functionality with RANDOM in Teradata.
1. It Needs Max_Limit like RANDOM(1,2)
2. The results Obtained are not same always..For Example when i execute RANDOM(1,2) i get 1 once and 2 the next time. In one way it makes sense that we are asking for RANDOM value. :-(
3. The Results are integers, Ofcourse we can CAST it but i would like to get a decimal value between 0 and 1
Which makes me doubt if this is the right function to use to get the TSQL RAND Functionality. Could someone Please let me know if any other thoughts/ways to acheive the same functionality as TSQL RAND?
you can not controll the seed in teradata, so you can not controll the random number generation.
But beside this
cast(random(0,1000000000) as decimal(20,10))/1000000000 might give you what you need.
The Result of it changes all the time.
SEL cast(random(0,1000000000) as decimal(20,10))/1000000000;
My need is that the result for a given value should always be same and the result should be between 0 and 1.
TSQL RANDOM works as :
SELECT RAND(9) would always return 0.713741056982989;
TD RANDOM works as designed, TSQL RAND works as designed, they're just designed differently.
You need to implement a UDF when you need similar output in TD, but unless you get the source code of the TSQL RAND you'll never get the same output.
You could also do some calculations based on HASHROW/HASHBUCKET, but the main questions is:
Why do you need the functionality of RAND?