Re: [sqlite] Date and age calculations

2009-09-16 Thread D. Richard Hipp
On Sep 17, 2009, at 12:53 AM, Igor Tandetnik wrote: > >> Is there >> any way I can make the SELECT more precise? > > Try this: > > WHERE ( date('now') - birth - (substr(date('now'), 6) < substr(birth, > 6)) ) >BETWEEN 12 and 24 > I didn't think about the BETWEEN operator. How about this:

Re: [sqlite] Date and age calculations

2009-09-16 Thread D. Richard Hipp
On Sep 16, 2009, at 11:49 PM, Craig Smith wrote: > Hello: > > I am having difficulties getting precise returns using a SELECT that > calculates the age of persons in my table. All dates are -MM-DD, > here is my SELECT statement: > > SELECT db_id, Full_name, round((SELECT julianday('now') - j

Re: [sqlite] Date and age calculations

2009-09-16 Thread Igor Tandetnik
Craig Smith wrote: > I am having difficulties getting precise returns using a SELECT that > calculates the age of persons in my table. All dates are -MM-DD, > here is my SELECT statement: > > SELECT db_id, Full_name, round((SELECT julianday('now') - julianday > (birth))/365,1) FROM members WHE

Re: [sqlite] Date and age calculations

2009-09-16 Thread Bart Smissaert
This is a VB function I use that will give you the idea. I think I got this with help from Dennis Cote. Public Function AgeAtISO8601Date(ByVal strDOB As String, _ ByVal strDate As String, _ Optional ByVal strAlias As String) As Str

[sqlite] Date and age calculations

2009-09-16 Thread Craig Smith
Hello: I am having difficulties getting precise returns using a SELECT that calculates the age of persons in my table. All dates are -MM-DD, here is my SELECT statement: SELECT db_id, Full_name, round((SELECT julianday('now') - julianday (birth))/365,1) FROM members WHERE date ('now') -

[sqlite] building permanently loaded extensions

2009-09-16 Thread P Kishor
pursuant to a recent email, I am wondering if I can build extension-functions.c so that it is permanently available within sqlite library, and not just when loaded manually via a command. If I can, could someone please guide me t

[sqlite] Convention for column type names?

2009-09-16 Thread Dan Bishop
I understand that SQLite lets you use arbitrary names for column types, and all that's significant is a few substrings like "INT", "CHAR", and "BLOB". But what's the common practice? Do you declare everything as INTEGER, TEXT, BLOB, REAL, or NUMERIC for a one-to-one match with the actual type

[sqlite] Force the use of a specified index?

2009-09-16 Thread Bart Smissaert
Is it possible to tell SQLite to use a specified index? I know you can use the + to excludes fields being used in an index, but this doesn't help me in this particular case. I remember a discussion about this and that this option might be added to SQLite, but couldn't find it anywhere. RBS ___

Re: [sqlite] Two feature requests

2009-09-16 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tim Romano wrote: > Requesting these here, since I'm not quite sure how to go about it via > the WIKI (do you simply edit the request list there and prepend|append > your request to the list?) Generally you should enter them as tickets setting the t

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

2009-09-16 Thread Igor Tandetnik
Gert Cuykens wrote: > select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty > from PRODUCTS t left join ORDERS o on t.pid = o.pid group by t.pid > > Works also thanks. > So you do not believe the following has a performance penalty ? > > SELECT pid, txt, price, qty-coalesce((SELEC

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

2009-09-16 Thread Gert Cuykens
On Wed, Sep 16, 2009 at 9:50 PM, Igor Tandetnik wrote: > Gert Cuykens > wrote: >> On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik >> wrote: >>> Gert Cuykens >>> wrote: On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik wrote: >>> > Perhaps your query could be a bit clearer when writ

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

2009-09-16 Thread Igor Tandetnik
Gert Cuykens wrote: > On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik > wrote: >> Gert Cuykens >> wrote: >>> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik >>> wrote: >> Perhaps your query could be a bit clearer when written this way: select t.pid, t.txt, t.price, t.qty - IFNULL

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

2009-09-16 Thread Gert Cuykens
On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik wrote: > Gert Cuykens > wrote: >> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik >> wrote: > >>> Perhaps your query could be a bit clearer when written this way: >>> >>> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) >>> onhand_qty fr

Re: [sqlite] Sqlite uses a lot of memory during a delete operation

2009-09-16 Thread shankar m
Maybe LIMIT clause can be used. Please check the below link. http://www.sqlite.org/lang_delete.html Regards Shankar On Wed, Sep 9, 2009 at 5:16 PM, P Kishor wrote: > On Tue, Sep 8, 2009 at 10:48 PM, Benjamin Rutt wrote: > > On Mon, Sep 7, 2009 at 12:28 PM, P Kishor wrote: > > > >> Find out i

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

2009-09-16 Thread Igor Tandetnik
Gert Cuykens wrote: > On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik > wrote: >> Perhaps your query could be a bit clearer when written this way: >> >> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) >> onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid > > This does not

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

2009-09-16 Thread Gert Cuykens
On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik wrote: > Gert Cuykens > wrote: >>   SELECT t.pid, >>          t.txt, >>          t.price, >>          t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty' >>     FROM PRODUCTS t >> LEFT JOIN (SELECT o.pid, >>                  SUM(o.qty) 'qty_sold' >>        

Re: [sqlite] Select records by specific YEAR

2009-09-16 Thread Simon Slavin
On 16 Sep 2009, at 6:05pm, vbshar wrote: > I think the problem is in field format ou data format, because if i > insert > data from my app SQLite does not return any value, but if i insert > data with > the manager "SQLite developer"; the query works... Your definition >> BIRTH à DATETIME

Re: [sqlite] Select records by specific YEAR

2009-09-16 Thread Pavel Ivanov
Maybe. Also there's a question when I wanted to ask in the first letter too: where did you get function year() from? I don't see it in http://www.sqlite.org/lang_datefunc.html and my sqlite3 doesn't see it too: sqlite> select year(date('now')); SQL error: no such function: year Maybe it's SQLite

Re: [sqlite] Select records by specific YEAR

2009-09-16 Thread vbshar
I think the problem is in field format ou data format, because if i insert data from my app SQLite does not return any value, but if i insert data with the manager "SQLite developer"; the query works... Pavel Ivanov-2 wrote: > > Did you try to put 1946 without quotes? > > Pavel > > On Wed, Sep

Re: [sqlite] Select records by specific YEAR

2009-09-16 Thread Pavel Ivanov
Did you try to put 1946 without quotes? Pavel On Wed, Sep 16, 2009 at 12:21 PM, vbshar wrote: > > Hi, > > I have a table called PEOPLE with 2 fiels: > > NAME à TEXT 40 > BIRTH à DATETIME > > With some records > > I want to filter all people with BIRTH = 1946, I’m trying this: > > SELECT * FROM P

[sqlite] Select records by specific YEAR

2009-09-16 Thread vbshar
Hi, I have a table called PEOPLE with 2 fiels: NAME à TEXT 40 BIRTH à DATETIME With some records I want to filter all people with BIRTH = 1946, I’m trying this: SELECT * FROM PEOPLE WHERE YEAR(PEOPLE.BIRTH)=’1946’; There are several people in this year, but recordset all time returns 0 recor

Re: [sqlite] Creating custom function for recursive queries

2009-09-16 Thread Igor Tandetnik
Marcel Strittmatter wrote: > I implemented a custom function that returns a comma separated list of > primary keys as a string by making recursive queries. This works well > if I don't use subqueries. But I like to use subqueries like this > > SELECT * FROM users WHERE id IN (SELECT parents('relat

Re: [sqlite] Temporal SQLite database

2009-09-16 Thread Jean-Christophe Deschamps
Doug, At 19:47 15/09/2009, you wrote: ´¯¯¯ >I'm not sure if you are looking to make a entry unique, or determine >the order in which the entries occurred. In either case, be aware - >time can go *backwards* on a system, especially if it is being syncd >to an outside source such as with NTP. > >

[sqlite] Best approach for storing not-so-small BLOBs per record

2009-09-16 Thread Itamar Syn-Hershko
Hi all, I'm in the design phase of an application with SQLite backend. The SQLite file will hold a table of about 20K records initially, and a few several other small tables. About 75% of the records in the large table will have binary data associated with it. My main question is which one of the

Re: [sqlite] Schema and database layout

2009-09-16 Thread Kavita Raghunathan
- Original Message - From: "P Kishor" To: "General Discussion of SQLite Database" Sent: Wednesday, September 16, 2009 9:00:29 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Schema and database layout >Google for help with designing relational databases. You will get 36 >million

Re: [sqlite] Temporal SQLite database

2009-09-16 Thread Alberto Simões
Hello, Doug Thanks for your message. That helped a lot. ambs On Tue, Sep 15, 2009 at 6:47 PM, Douglas E. Fajardo < dfaja...@beyondtrust.com> wrote: > I'm not sure if you are looking to make a entry unique, or determine the > order in which the entries occurred. In either case, be aware - time ca

Re: [sqlite] Creating custom function for recursive queries

2009-09-16 Thread Simon Davies
2009/9/16 Marcel Strittmatter : > Hi > > I implemented a custom function that returns a comma separated list of > primary keys as a string by making recursive queries. This works well > if I don't use subqueries. But I like to use subqueries like this > > SELECT * FROM users WHERE id IN (SELECT par

[sqlite] Creating custom function for recursive queries

2009-09-16 Thread Marcel Strittmatter
Hi I implemented a custom function that returns a comma separated list of primary keys as a string by making recursive queries. This works well if I don't use subqueries. But I like to use subqueries like this SELECT * FROM users WHERE id IN (SELECT parents('relations', 3)); Below some examp

Re: [sqlite] Schema and database layout

2009-09-16 Thread Pavel Ivanov
> Would this be a better design ? That would be definitely a better design at least because you will be able to keep only one prepared statement where one of bindings will be your entity id. Also you will be able to add more types of entities without changing database schema. Just don't forget t

Re: [sqlite] Schema and database layout

2009-09-16 Thread P Kishor
On Wed, Sep 16, 2009 at 8:57 AM, Kavita Raghunathan wrote: > > - Original Message - > From: "Pavel Ivanov" > To: "General Discussion of SQLite Database" > Sent: Wednesday, September 16, 2009 6:40:18 AM GMT -06:00 US/Canada Central > Subject: Re: [sqlite] Schema and database layout > >> K

Re: [sqlite] Schema and database layout

2009-09-16 Thread Kavita Raghunathan
- Original Message - From: "Pavel Ivanov" To: "General Discussion of SQLite Database" Sent: Wednesday, September 16, 2009 6:40:18 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Schema and database layout > KR: The 10 tables contain different data, but instead of doing this statem

[sqlite] Two feature requests

2009-09-16 Thread Tim Romano
Requesting these here, since I'm not quite sure how to go about it via the WIKI (do you simply edit the request list there and prepend|append your request to the list?) 1. An IFEMPTY(a,b) operator would be a convenience, analogous to IFNULL(a,b). It would return the first non-null, non-empty-st

Re: [sqlite] Schema and database layout

2009-09-16 Thread Pavel Ivanov
> KR: The 10 tables contain different data, but instead of doing this statement > (example below) over > and over, I want to just pass this table "type" because all my tables look > exactly > like table "TypeNumbers", but they have different data, depending on the > entity-id. You better stop t

Re: [sqlite] Temporal SQLite database

2009-09-16 Thread Douglas E. Fajardo
I'm not sure if you are looking to make a entry unique, or determine the order in which the entries occurred. In either case, be aware - time can go *backwards* on a system, especially if it is being syncd to an outside source such as with NTP. Normally the 'jitter' is under a second, but excep