All Forums Data Modeling
alscor 2 posts Joined 06/14
11 Jun 2014
Creating a table of a given size filled with random numbers

How to generate a table of a given size (say, 1 million rows) with a column that represent a random number?
I need to create a table, which will have two columns: row_num, rand_num:
Row_num rand_num
             1           23
             2             3
             3           12
             4           89
             5            …
I can use random function random (1,100) if I already have a table with a certain number of rows. My struggle is to do it from scratch.

dnoeth 4628 posts Joined 11/04
11 Jun 2014
WITH cte (n) AS
 (
   SELECT day_of_calendar AS n
   FROM sys_calendar.CALENDAR
   WHERE n BETWEEN 1 AND 1000
 )
SELECT
   (t1.n -1) * 1000 + t2.n
   ,RANDOM(1,100)
FROM cte AS t1, cte AS t2

 

Dieter

alscor 2 posts Joined 06/14
13 Jun 2014

Wow, it works, and it's clever. I was thinking to use sys_calendar.calendar table as a reference, but could not figure out how to expand beyond its limit. This is a good example of usefulness of the carthesian join.
I did not quite understand how WITH statement works. Can you explain it, please, or provide reference?
Perhaps, it is equivalent to:
SELECT (t1.n -1) * 1000 + t2.n ,RANDOM(1,100) FROM
(SELECT day_of_calendar AS n FROM sys_calendar.CALENDAR WHERE n BETWEEN 1 AND 1000) t1,
(SELECT day_of_calendar AS n FROM sys_calendar.CALENDAR WHERE n BETWEEN 1 AND 1000) t2
In any case, thanks so much!
 

dnoeth 4628 posts Joined 11/04
15 Jun 2014

Yes, this is exactly the same query.
WITH is mainly usefull if you need to use the same Derived Table multiple times.
Otherwise it's just by habit, Teradata only supported the WITH syntax in a limited way (mainly for recursive queries), (most of) those limitations have been lifted, but I still hardly use them. Other people coming from DB2 or SQL Server only use them instead of Derived Tables.

Dieter

You must sign in to leave a comment.