Data Modeling Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 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. Tags: random numbers 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. Active Posters