Re: [sqlite] Implicitly creating a table containing the first N consecutive integers?
On Tue, Jun 07, 2011 at 06:12:55PM -0400, Richard Hipp scratched on the wall: > On Tue, Jun 7, 2011 at 5:44 PM, Guenther Brunthaler > wrote: > > See > http://www.sqlite.org/src/artifact/6129adfbe7c7444f2e60cc785927f3aa74e12290 > for an example implementation of a virtual table that does something > pretty much like the above. We've recently started using it some for > our test cases. Ooo... that's even more nifty and simple. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implicitly creating a table containing the first N consecutive integers?
On Tue, Jun 07, 2011 at 11:44:20PM +0200, Guenther Brunthaler scratched on the wall: > Hi all, > > I frequently need a table in my queries for several kinds of JOIN > operations which contains just the integers from 1 to N in its rows. > > I. e. > > SELECT n FROM int_seq where n <= 5; > 1 > 2 > 3 > 4 > 5 > > However, I would like to achieve the same effect without actually > creating such a table "int_seq". Can you use an IN expression, rather than a JOIN? If you're doing this from C/C++, you might also check out the "intarray" code. It is designed to be used with IN, but would work for this as well. It basically creates a virtual table that can be bound to an in-memory array of integers. You could setup a large array of integers and then bind it to the required length. See the notes in the header file. src/test_intarray.h http://www.sqlite.org/cgi/src/artifact/489edb9068bb926583445cb02589344961054207 src/test_intarray.c http://www.sqlite.org/cgi/src/artifact/d879bbf8e4ce085ab966d1f3c896a7c8b4f5fc99 -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implicitly creating a table containing the first N consecutive integers?
On Tue, Jun 7, 2011 at 5:44 PM, Guenther Brunthaler wrote: > Hi all, > > I frequently need a table in my queries for several kinds of JOIN > operations which contains just the integers from 1 to N in its rows. > > I. e. > > SELECT n FROM int_seq where n <= 5; > 1 > 2 > 3 > 4 > 5 > > However, I would like to achieve the same effect without actually > creating such a table "int_seq". > > It it possible in SQLite to create such a table implicitly "on the fly" > using some sort of recursive view/query or built-in special function? > See http://www.sqlite.org/src/artifact/6129adfbe7c7444f2e60cc785927f3aa74e12290for an example implementation of a virtual table that does something pretty much like the above. We've recently started using it some for our test cases. > > Regards, > > Guenther > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implicitly creating a table containing the first N consecutive integers?
On Jun 7, 2011, at 11:44 PM, Guenther Brunthaler wrote: > It it possible in SQLite to create such a table implicitly "on the fly" > using some sort of recursive view/query or built-in special function? The short of it: no, not out-of-the-box. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users