The integers table
I want to write one query to get serial dates, like 1/1/2004, 1/2/2004... I know repeating "select dateadd(d,n,'1/1/2004')" can get the result. But how about a simple method?
My favourite solution for this type of problem uses an integers table. Here's the code to create it:
create table integers (i integer) insert into integers (i) values (0) insert into integers (i) values (1) insert into integers (i) values (2) insert into integers (i) values (3) insert into integers (i) values (4) insert into integers (i) values (5) insert into integers (i) values (6) insert into integers (i) values (7) insert into integers (i) values (8) insert into integers (i) values (9)
Those 10 rows are sufficient to create, with one or more cross joins, whatever range of integers you need. For example, you could generate the numbers 0 through 999 with a triple cross join like this:
select 100*hundreds.i + 10*tens.i + units.i as iii from integers hundreds , integers tens , integers units
Why iii? Because that reminds me that it's a number up to 999. Note that the query uses table list syntax without join conditions, thus joining every row from all three "copies" of the integers table with every other row. This table list syntax for a cross join will work in any database. In effect, it is the same query as the following one using explicit JOIN syntax:
select 100*hundreds.i + 10*tens.i + units.i as iii from integers hundreds cross join integers tens cross join integers units
The words hundreds, tens, and units are table aliases, required to distinguish the rows from the three different "copies" of the table that are present in the query. Usually I write h, t, and u, to make typing easier. Even more convenient is a view for this query:
create view integers999 (iii) as select 100*h.i+10*t.i+u.i from integers h cross join integers t cross join integers u
Now you can use the iii column in an expression to produce all the dates for this year, using a WHERE clause to restrict the upper end of the range:
select dateadd(d,iii,'2004-01-01') as thedate from integers999 where iii <= 365
Simple, eh? Note that there are 366 days in 2004, so '2004-01-01' + 0 days will be the first date, and '2004-01-01' + 365 days will be the last.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments