-- clear old numbers structure (careful here)
drop table if exists numbers;
-- create numbers utility structure, very basic integer primary key column n
create table numbers(n integer not null, primary key(n asc));
-- insert generated sequential integers
insert into numbers(n)
select rowid
from (
-- build 1,000,000 rows using Cartesian product
select 1
from (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) a, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) b, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) c, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) d, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) e, (
select 0 union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9
) f
) derived;
VoilĂ !
attach database '{file name}' as {database-name};
The single quotes are not required if your file name doesn't include an extension, but as a convention I have created mine as {database-name}.db so quotes are needed in my example.
attach database 'util.db' as util;
Note that the {database-name} here can be however you want to reference the attached database and doesn't restrict you to having to be the same name as the file. A good example of this is that the primary database connection has a name of 'main' by default. To take a look at the attached databases, you would use the following command:
.databases
Aside from being a useful reminder of the schema name given to your database file(s) you have attached or double-checking a given database is attached before you query, it may be a necessary exercise if you already have connections equal to SQLITE_MAX_ATTACHED. If you need to detach a database, you would use:
detach database {database-name};
Where {database-name} is the same schema name you assigned during attachment which you will see displayed as name when viewing the results of .databases.
select n from util.numbers limit 10;
I want to produce a list of recurring items from a table where one record exists.
e.g. keep adding records which accumulate and increase the due date
select a.ID, a.desc
, date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype) as due
, a.[price]
from billsndeposits a
inner join util_nums b -- <<<< this would be util.numbers
on date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype) < date('2011-01-01')
where not (intervaltype = 'once' or interval = 0)
order by due, id;
Using the date() function, this simulates a loop by replicating rows of the bills and deposits table by a number (n) from our numbers table that when added to the start date in a specific interval of time resulted in a date that fell within a given time period. This select was then used to create a new table, but as you can see was useful in putting into a single statement what would have normally been a multi-line statement with a loop.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)