Equivalent of TSQL RAND Function in Teradata

Database
Enthusiast

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?

Thanks,

Vinay

3 REPLIES
Supporter

Re: Equivalent of TSQL RAND Function in Teradata

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.

Enthusiast

Re: Equivalent of TSQL RAND Function in Teradata

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

etc....,

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;

Thanks,

Vinay

Junior Contributor

Re: Equivalent of TSQL RAND Function in Teradata

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?

DIeter