Click here to Skip to main content
12,757,627 members (36,536 online)
Click here to Skip to main content
Add your own
alternative version

Stats

13.6K views
6 bookmarked
Posted 17 Dec 2010

Use SQL to generate large data sequence

, 19 Feb 2015 CPOL
Rate this:
Please Sign up or sign in to vote.
Generate large data series by using Cartesian Product and small data table

The proposed solution demonstrates the concept of generating a large data series by using structured query language (SQL) and relatively small data tables, thus dramatically reducing the overall 'digital footprint' (rather important design consideration for any mobile/compact/embedded solutions).
 
Sample coding technique to demonstrate the concept utilizes a small table (Table1) with just 10 records, containing integer numbers: 0,1,2,3,...9, and a single SQL query to generate a virtual time line, which spans for 10,000 days, or roughly 27 years moving backward from the current date (Access SQL, Listing 1):
 

SELECT Now()-CLng([Table1].[Num] & [Table1_1].[Num] & [Table1_2].[Num] & [Table1_3].[Num]) AS TimeSeries
FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2, Table1 AS Table1_3
ORDER BY Now()-CLng([Table1].[Num] & [Table1_1].[Num] & [Table1_2].[Num] & [Table1_3].[Num]) DESC;

 
The logic behind the solution is rather straightforward. The query creates 2 virtual tables (Table_1, Table_2 and Table_3) in addition to the 'concrete' permanent Table1, and then applies the so-called 'Cartesian product' technique, concatenating every record from all 4 tables (permanent and virtual as well). Then it applies the data type conversion to long integer, which represents the date, and finally, sorts the date series of 10,000 records, going from current date backward in descending order.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

DrABELL
President Infosoft International Inc
United States United States
Dr. Alexander Bell is a seasoned full-stack Software Engineer (Win/Web/Mobile). He holds PhD in EE/IT, authored 37 inventions and published 300+ technical articles. Currently focused on HTML5/CSS3, Javascript, .NET/WPF/C#, Angular.js, SQL, 'Big Data', Machine Learning, AI, IoT. Alex participated in App Innovation Contests (AIC 2102/2013) with multiple winning submissions. Portfolio samples:

  1. Real-time NY Bus Tracking Web App (IoT)

  2. Integrated Sensors Hub (IMU) Testing Web Page

  3. Semaphon™ semantic phone num-to-text converter

  4. Educational Web Portal

  5. Free Online NY Payroll Tax Calculator

  6. WebTV powered by YouTube Player powered by .NET API (#1 on Google)

  7. Top-50 Digital Cameras (by iMark-DCAM rating engine)

  8. Pure CSS3 Slide Show

  9. Inflation Calculator

  10. Multilingual Geocoder with Interactive Map

  11. Online Semantic Analyzer (Concordance Calculator)

  12. Advanced CSS3 Table Formatting


You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionSQL Server Pin
Member 1041007620-Feb-15 15:33
memberMember 1041007620-Feb-15 15:33 
GeneralFYI: short URL to the full article: http://exm.nr/SQLSeq Pin
DrABELL4-Jan-11 10:36
memberDrABELL4-Jan-11 10:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170217.1 | Last Updated 19 Feb 2015
Article Copyright 2010 by DrABELL
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid