Database

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-18-2008
03:24 AM

08-18-2008
03:24 AM

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

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 6

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-18-2008
03:29 AM

08-18-2008
03:29 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-18-2008
03:38 AM

08-18-2008
03:38 AM

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

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

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-18-2008
03:43 AM

08-18-2008
03:43 AM

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

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

Regards,

Adeel

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-18-2008
04:08 AM

08-18-2008
04:08 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-05-2009
12:06 PM

06-05-2009
12:06 PM

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!

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-05-2009
01:41 PM

06-05-2009
01:41 PM

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;

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

random_nbr;