Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

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

- 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

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

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

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

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

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

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;

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.