Styjun

How do I talk to my wife about unrealistic expectations?

How can I use my cell phone's light as a reading light?

Need a non-volatile memory IC with near unlimited read/write operations capability

Passwordless authentication - how and when to invalidate a login code

Is "wissen" the only verb in German to have an irregular present tense?

Why does Trump want a citizenship question on the census?

Draw a diagram with rectangles

How to understand flavors and when to use combination of them?

My professor has told me he will be the corresponding author. Will it hurt my future career?

Why did Robert F. Kennedy loathe Lyndon B. Johnson?

Deck of Cards with Shuffle and Sort functionality

Redirecting stderr using exec

Why am I getting unevenly-spread results when using $RANDOM?

Interpretation of non-significant results as "trends"

run bash scripts in folder all at the same time

US citizen traveling with Peruvian passport

My previous employer committed a severe violation of the law and is also being sued by me. How do I explain the situation to future employers?

Computer name naming convention for security

Can the word "desk" be used as a verb?

This LM317 diagram doesn't make any sense to me

Users forgotting to regenerate PDF before sending it

Strong Password Detection in Python

Write a function

How do I separate enchants from items?

How can I repeat this query 100 times?

How can I prevent SQL injection in PHP?SQL update from one Table to another based on a ID matchCan I concatenate multiple MySQL rows into one field?Inserting multiple rows in a single SQL query?SQL exclude a column using SELECT * [except columnA] FROM tableA?Select n random rows from SQL Server tableInsert, on duplicate update in PostgreSQL?How do I UPDATE from a SELECT in SQL Server?How to query MongoDB with “like”?What are the options for storing hierarchical data in a relational database?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have this query to generate a random number from one table

select DBMS_RANDOM.value as val
from c4_trt
where rownum=1;

Now I want to generate 100 random numbers from this table.

Please note: I want to generate a random number from this specific table, not from dual. But I'm not sure if I can generate a random number from a specific table.

Thanks all!

sql oracle

share|improve this question

asked Mar 25 at 22:32

LionLion

111 bronze badge

add a comment | 

0

I have this query to generate a random number from one table

select DBMS_RANDOM.value as val
from c4_trt
where rownum=1;

Now I want to generate 100 random numbers from this table.

Please note: I want to generate a random number from this specific table, not from dual. But I'm not sure if I can generate a random number from a specific table.

Thanks all!

sql oracle

share|improve this question

asked Mar 25 at 22:32

LionLion

111 bronze badge

add a comment | 

I have this query to generate a random number from one table

select DBMS_RANDOM.value as val
from c4_trt
where rownum=1;

Now I want to generate 100 random numbers from this table.

Please note: I want to generate a random number from this specific table, not from dual. But I'm not sure if I can generate a random number from a specific table.

Thanks all!

sql oracle

share|improve this question

asked Mar 25 at 22:32

LionLion

111 bronze badge

share|improve this question

asked Mar 25 at 22:32

LionLion

111 bronze badge

share|improve this question

asked Mar 25 at 22:32

LionLion

111 bronze badge

asked Mar 25 at 22:32

LionLion

111 bronze badge

asked Mar 25 at 22:32

LionLion

111 bronze badge

2 Answers
2

active

oldest

votes

2

You stated in your question that you did NOT want to see a solution that utilizes the Oracle's dual dummy table. Unless your table already has 100 + records in the table you are selecting from I currently don't see a way for you to get 100 records showing random numbers unless you use Connect By Level.

Below I've provided TWO answers that would get you 100 Random Numbers in 100 Rows of output. The FIRST example uses the dual table and Connect By Level using LEVEL to limit the rows CONNECT BY LEVEL <= 100. The SECOND example uses rownum and limits the rows using rownum <= 100.

Using the dual table

SELECT LEVEL, floor(dbms_random.value(1,11)) AS rand_num /* Returns random numbers 1 thru 10 */
FROM dual
CONNECT BY LEVEL <= 100
;

Using any other table
This example uses Oracles all_objects Dictionary View as it returns way more than 100 Rows so I decided to work with this but know that any Table/View that returns more than 100 rows will work in this example.

SELECT floor(dbms_random.value(1,11)) AS rand_num, /* Returns random numbers 1 thru 10 */
 rownum as at_row_num
FROM all_objects
WHERE rownum <= 100
;

The Image below shows what the output looks like when using the dual table example.

share|improve this answer

edited Mar 25 at 23:19

answered Mar 25 at 23:13

Code NoviceCode Novice

71744 silver badges2020 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thank you very much! yes, my table c4_trt has 10M rows, but i do want to get a random number generated from this table and repeat 100 times.
  
  – Lion
  Mar 27 at 16:35
  

  
  
  
  

add a comment | 

1

Not sure why you need or want to but this will work if you have at least 100
rows in your table and that's only to keep rownum happy. The table you use is irrelevant.

select DBMS_RANDOM.value as val from c4_trt where rownum =< 100;

share|improve this answer

answered Mar 25 at 23:10

Jim CastroJim Castro

46411 silver badge55 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55347345%2fhow-can-i-repeat-this-query-100-times%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

You stated in your question that you did NOT want to see a solution that utilizes the Oracle's dual dummy table. Unless your table already has 100 + records in the table you are selecting from I currently don't see a way for you to get 100 records showing random numbers unless you use Connect By Level.

Below I've provided TWO answers that would get you 100 Random Numbers in 100 Rows of output. The FIRST example uses the dual table and Connect By Level using LEVEL to limit the rows CONNECT BY LEVEL <= 100. The SECOND example uses rownum and limits the rows using rownum <= 100.

Using the dual table

SELECT LEVEL, floor(dbms_random.value(1,11)) AS rand_num /* Returns random numbers 1 thru 10 */
FROM dual
CONNECT BY LEVEL <= 100
;

Using any other table
This example uses Oracles all_objects Dictionary View as it returns way more than 100 Rows so I decided to work with this but know that any Table/View that returns more than 100 rows will work in this example.

SELECT floor(dbms_random.value(1,11)) AS rand_num, /* Returns random numbers 1 thru 10 */
 rownum as at_row_num
FROM all_objects
WHERE rownum <= 100
;

The Image below shows what the output looks like when using the dual table example.

share|improve this answer

edited Mar 25 at 23:19

answered Mar 25 at 23:13

Code NoviceCode Novice

71744 silver badges2020 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thank you very much! yes, my table c4_trt has 10M rows, but i do want to get a random number generated from this table and repeat 100 times.
  
  – Lion
  Mar 27 at 16:35
  

  
  
  
  

add a comment | 

2

You stated in your question that you did NOT want to see a solution that utilizes the Oracle's dual dummy table. Unless your table already has 100 + records in the table you are selecting from I currently don't see a way for you to get 100 records showing random numbers unless you use Connect By Level.

Below I've provided TWO answers that would get you 100 Random Numbers in 100 Rows of output. The FIRST example uses the dual table and Connect By Level using LEVEL to limit the rows CONNECT BY LEVEL <= 100. The SECOND example uses rownum and limits the rows using rownum <= 100.

Using the dual table

SELECT LEVEL, floor(dbms_random.value(1,11)) AS rand_num /* Returns random numbers 1 thru 10 */
FROM dual
CONNECT BY LEVEL <= 100
;

Using any other table
This example uses Oracles all_objects Dictionary View as it returns way more than 100 Rows so I decided to work with this but know that any Table/View that returns more than 100 rows will work in this example.

SELECT floor(dbms_random.value(1,11)) AS rand_num, /* Returns random numbers 1 thru 10 */
 rownum as at_row_num
FROM all_objects
WHERE rownum <= 100
;

The Image below shows what the output looks like when using the dual table example.

share|improve this answer

edited Mar 25 at 23:19

answered Mar 25 at 23:13

Code NoviceCode Novice

71744 silver badges2020 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thank you very much! yes, my table c4_trt has 10M rows, but i do want to get a random number generated from this table and repeat 100 times.
  
  – Lion
  Mar 27 at 16:35
  

  
  
  
  

add a comment | 

You stated in your question that you did NOT want to see a solution that utilizes the Oracle's dual dummy table. Unless your table already has 100 + records in the table you are selecting from I currently don't see a way for you to get 100 records showing random numbers unless you use Connect By Level.

Below I've provided TWO answers that would get you 100 Random Numbers in 100 Rows of output. The FIRST example uses the dual table and Connect By Level using LEVEL to limit the rows CONNECT BY LEVEL <= 100. The SECOND example uses rownum and limits the rows using rownum <= 100.

Using the dual table

SELECT LEVEL, floor(dbms_random.value(1,11)) AS rand_num /* Returns random numbers 1 thru 10 */
FROM dual
CONNECT BY LEVEL <= 100
;

Using any other table
This example uses Oracles all_objects Dictionary View as it returns way more than 100 Rows so I decided to work with this but know that any Table/View that returns more than 100 rows will work in this example.

SELECT floor(dbms_random.value(1,11)) AS rand_num, /* Returns random numbers 1 thru 10 */
 rownum as at_row_num
FROM all_objects
WHERE rownum <= 100
;

The Image below shows what the output looks like when using the dual table example.

share|improve this answer

edited Mar 25 at 23:19

answered Mar 25 at 23:13

Code NoviceCode Novice

71744 silver badges2020 bronze badges

SELECT LEVEL, floor(dbms_random.value(1,11)) AS rand_num /* Returns random numbers 1 thru 10 */
FROM dual
CONNECT BY LEVEL <= 100
;

SELECT floor(dbms_random.value(1,11)) AS rand_num, /* Returns random numbers 1 thru 10 */
 rownum as at_row_num
FROM all_objects
WHERE rownum <= 100
;

share|improve this answer

edited Mar 25 at 23:19

answered Mar 25 at 23:13

Code NoviceCode Novice

71744 silver badges2020 bronze badges

answered Mar 25 at 23:13

Code NoviceCode Novice

71744 silver badges2020 bronze badges

answered Mar 25 at 23:13

Code NoviceCode Novice

71744 silver badges2020 bronze badges

1

Not sure why you need or want to but this will work if you have at least 100
rows in your table and that's only to keep rownum happy. The table you use is irrelevant.

select DBMS_RANDOM.value as val from c4_trt where rownum =< 100;

share|improve this answer

answered Mar 25 at 23:10

Jim CastroJim Castro

46411 silver badge55 bronze badges

add a comment | 

1

Not sure why you need or want to but this will work if you have at least 100
rows in your table and that's only to keep rownum happy. The table you use is irrelevant.

select DBMS_RANDOM.value as val from c4_trt where rownum =< 100;

share|improve this answer

answered Mar 25 at 23:10

Jim CastroJim Castro

46411 silver badge55 bronze badges

add a comment | 

Not sure why you need or want to but this will work if you have at least 100
rows in your table and that's only to keep rownum happy. The table you use is irrelevant.

select DBMS_RANDOM.value as val from c4_trt where rownum =< 100;

share|improve this answer

answered Mar 25 at 23:10

Jim CastroJim Castro

46411 silver badge55 bronze badges

select DBMS_RANDOM.value as val from c4_trt where rownum =< 100;

share|improve this answer

answered Mar 25 at 23:10

Jim CastroJim Castro

46411 silver badge55 bronze badges

answered Mar 25 at 23:10

Jim CastroJim Castro

46411 silver badge55 bronze badges

answered Mar 25 at 23:10

Jim CastroJim Castro

46411 silver badge55 bronze badges