All Forums Database
5485993v 19 posts Joined 08/11
12 Jun 2012
Equivalent of TSQL RAND Function in Teradata

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?




ulrich 816 posts Joined 09/09
12 Jun 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

5485993v 19 posts Joined 08/11
14 Jun 2012

Hi -

The Result of it changes all the time.
SEL cast(random(0,1000000000) as decimal(20,10))/1000000000;

Result1: 0.9268257100
Result2: 0.8241862190

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;



dnoeth 4628 posts Joined 11/04
14 Jun 2012

Hi Vinay,
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?



You must sign in to leave a comment.