How to generate random integer numbers by using a function

Database
Enthusiast

How to generate random integer numbers by using a function

Hi

I want to generate random integer numbers in Teradata.
In SQL, we have random function rand(), but it gives only the whole number between 0 and 1.

1. Like wise do we have any function to generate random numbers in Teradata?
2. And also similarly i want to select random records from a set of records in a table.

Does any one help me to do the above two tasks?

Thanks
Dileep
6 REPLIES
Teradata Employee

Re: How to generate random integer numbers by using a function

Hello,

You can use following for random number:

- SELECT Random("min value", "max value"); e.g.: SELECT Random(0, 10);

For selecting random rows, you can use:

- SELECT * from Table1 Sample 10;

HTH.

Regards,

Adeel
Enthusiast

Re: How to generate random integer numbers by using a function



Does the min and max values include in the range??

Thanks
Teradata Employee

Re: How to generate random integer numbers by using a function

Yes, you could have tested it yourself as well. :)

For verification, try this several times: "SELECT Random(1, 2);"

Regards,

Adeel
Enthusiast

Re: How to generate random integer numbers by using a function



it's ok .... for time being i don't have the teradata sql assistant installed in my machine... i will get it soon.
can i have the syntax for creating an user defined function in teradata?
i want to include the above statements in a function and re-use that function.

Thanks

Enthusiast

Re: How to generate random integer numbers by using a function

Hi,

Is there a way on how we can make the results in decimal as well? I tried the SELECT Random(min,max) function but it only gave me whole numbers unlike in ORACLE. For example, the one shown below:

--------ORACLE--------vs-------TERADATA
Row1: 0.546476429------------------1
Row2: 0.095457194------------------0
Row3: 0.254218001------------------1
Row4: 0.054972349------------------0
Row5: 0.978196729------------------1

thanks!
Enthusiast

Re: How to generate random integer numbers by using a function

Cast the result of RANDOM to float or decimal.

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