Re: [sqlite] Simple SQL question?
On 17-11-10 00:17, Petite Abeille wrote: > select t1.patient_id > fromtable1 t1 > join( > select table1.address, > min( table1.date_of_birth ) as date_of_birth > fromtable1 > group bytable1.address > ) > as t2 > joint2.address = t1.address 'join' should be: ONt2.address = t1.address > and t2.date_of_birth = t1.date_of_birth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
That is a strange construction and for now I haven't got it to work yet in my VB application. It does run though in Firefox SQLite manager. Maybe after all the SQL I came up with in the end wasn't that bad. RBS On Wed, Nov 17, 2010 at 12:09 AM, Igor Tandetnik wrote: > Bart Smissaert wrote: >> Have (simplified) a table like this: >> >> CREATE TABLE TABLE1( >> [PATIENT_ID] INTEGER PRIMARY KEY, >> [ADDRESS] TEXT, >> [DATE_OF_BIRTH] TEXT) >> >> DATE_OF_BIRTH is in the ISO8601 format -mm-dd >> >> Now I need a SQL to find the oldest patients living at all the >> different (unique) addresses, so this will be >> the patient with the lowest DATE_OF_BIRTH. I will need the PATIENT_ID >> of that patient and nil else. > > select (select PATIENT_ID from TABLE1 t1 > where t1.ADDRESS = t2.ADDRESS > order by DATE_OF_BIRTH limit 1) > from (select distinct ADDRESS from TABLE1) t2; > > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
Tried your SQL, but it doesn't look right and didn't run. Will see if I can alter it. RBS On Tue, Nov 16, 2010 at 11:17 PM, Petite Abeille wrote: > > On Nov 16, 2010, at 11:55 PM, Bart Smissaert wrote: > >> This seems to work fine, > > Then you are golden :) > >> but I am not sure if this SQL is correct and >> if the results will always be correct and have a feeling >> that there must be a better construction. > > > >> Any suggestions? > > Nothing very meaningful, but you could rewrite the 'in' clause as a 'join' to > avoid all these concatenations, e.g.: > > select t1.patient_id > from table1 t1 > join ( > select table1.address, > min( table1.date_of_birth ) as date_of_birth > from table1 > group by table1.address > ) > as t2 > join t2.address = t1.address > and t2.date_of_birth = t1.date_of_birth > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
What do you suggest should be the full SQL then? RBS On Wed, Nov 17, 2010 at 8:16 AM, luuk34 wrote: > On 17-11-10 00:17, Petite Abeille wrote: >> select t1.patient_id >> from table1 t1 >> join ( >> select table1.address, >> min( table1.date_of_birth ) as date_of_birth >> from table1 >> group by table1.address >> ) >> as t2 >> join t2.address = t1.address > > 'join' should be: > > ON t2.address = t1.address > > >> and t2.date_of_birth = t1.date_of_birth > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
On 17-11-10 09:58, Bart Smissaert wrote: > What do you suggest should be the full SQL then? > select t1.patient_id fromtable1 t1 join( select table1.address, min( table1.date_of_birth ) as date_of_birth fromtable1 group bytable1.address ) as t2 ON t2.address = t1.address and t2.date_of_birth = t1.date_of_birth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
On 17-11-10 10:00, luuk34 wrote: > On 17-11-10 09:58, Bart Smissaert wrote: >> What do you suggest should be the full SQL then? >> > select t1.patient_id > fromtable1 t1 > join( > select table1.address, > min( table1.date_of_birth ) as date_of_birth > fromtable1 > group bytable1.address > ) > as t2 > ON t2.address = t1.address > and t2.date_of_birth = t1.date_of_birth > i forgot the link: http://www.sqlite.org/syntaxdiagrams.html#join-constraint ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
Tried your SQL, but it doesn't run. Will fiddle it and see if I can make it work. RBS On Wed, Nov 17, 2010 at 9:00 AM, luuk34 wrote: > On 17-11-10 09:58, Bart Smissaert wrote: >> What do you suggest should be the full SQL then? >> > select t1.patient_id > from table1 t1 > join ( > select table1.address, > min( table1.date_of_birth ) as date_of_birth > from table1 > group by table1.address > ) > as t2 > ON t2.address = t1.address > and t2.date_of_birth = t1.date_of_birth > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Key Value pairs in a table
On 17-11-10 10:32, Arigead wrote: > Hi all, > I've started to use an existing Database with C source code. As I'm new > to > all this I can't moan about database design but I'm sure that Key Value pairs > in a Database table suits modern languages like Python down to the ground. It > ain't suiting me though ;-) > > I'm really struggling to find a solution to my problems so I though I might > try > here. There are two database tables that I'm interested which keep track of > contacts. > > One table "contacts" simply keeps contact_id which is unique > > A second table "contact_name" keeps track of key value pairs for the contacts: > > CREATE TABLE contacts_name > (contacts_name_id INTEGER PRIMARY KEY,contacts_id > REFERENCES contacts(contacts_id), > field_name TEXT, > value TEXT NOT NULL); > CREATE INDEX contacts_name_contacts_id ON contacts_name(contacts_id); > > > So in Contacts table I might have a few id's > 1 > 2 > > And in contacts_name I might have a few key value pairs: > > ID field_namevalue > 1 Name Tom > 1 Surname Jones > 2 Name Fred > 2 Surname Flintstone > > I didn't design this system and it don't seem ideal to me, coming from C, but > I'll have to get on with it. I decided that to make things simpler for my C I > could create a temporary table and populate it with contact_id, name and > Surname which is all that I'm interested in. > > So I created a new table with: > > create temporary table if not exists contacts_tmp > (contacts_id REFERENCES contacts(contacts_id), > Name TEXT, Surname TEXT) > > That's a database table I could work with ;-) Now I have to populate it with > the data from the existing contacts_name table so I get the contacts_id and > Name inserted with: > > INSERT INTO contacts_tmp (contacts_id, name) > SELECT contacts_id, value FROM contacts_name > WHERE field_name="Name" > > > The above statement works insofar as it populate id and name but I can't get > surname into my table. I've tried to write a second insert statement to pull > out the Surname tag from contacts_name but I just can't get it. I'm trying > something along the lines of: > > INSERT INTO contacts_tmp (Surname) > SELECT contacts_name.value FROM contacts_name where > contacts_name.field_name="Surname" > JOIN contacts_name ON contacts_tmp.contacts_id = contacts_name.contacts_id > > Appologies for the long first post but I can't find a solution. Any advice, > apart from redesigning the old tables would be greatefully received. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users select c.contacts_id, n.value as Name, s.value as Surname from contacts c left join contacts_name n on c.contacts_id=n.contacts_id and n.field_name='Name' left join contacts_name s on c.contacts_id=s.contacts_id and s.field_name='Surname' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange Corruption
.dump (3.6.23.1) and afterwards "sqlite3 /tmp/new.db < /tmp/dump.sql" with 3.7.2 worked and fixed the .backup problem (as expected). As I've already downgraded sqlite3 in our new firmware and patched the live-systems that were running with the new firmware I'll only have one machine to check whether the error comes back or not... So maybe I'll not be able to give feedback for a few weeks (as I can not enforce errors). However: if the error will not come back and could have to do with an already existing error in the database it would be quite interesting to know why integrity_check doesn't find the error before making a backup. (What means that a bug exists in any case: either in PRAGMA integrity_check or in the backup function). --- Pirmin Walthert Am 16.11.2010 14:22, schrieb Black, Michael (IS): > Sorry, I meant .dump > > Given what you're describing I think it's worth finding out if you've found > some bug in 3.7.2. > The docs say 3.7.2 fixed a long-standing corruption bug. I don't know if > that's related to this or not but sounds suspiciously close. > > So... > > #1 .dump the database > #2 .import in into 3.7.2 > #3 Run for a few days and see if you still get your backup problem. > > If still corrupt try 3.7.3 > > If it works then it sounds like the database was corrupt already and 3.7.2 > just hits it. > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert > Sent: Tue 11/16/2010 7:09 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] EXTERNAL:Re: Strange Corruption > > > > Well indeed it wasn't 3.7.X that created the database originally. But it > was always 3.7.2 that made the INSERTS/UPDATES that lead to the state in > which the database couldn't be backed up anymore. So what do you mean in > fact: 3.7.X maybe can't handle database structures created with older > versions?! > > Even after doing a vacuum which fixed the bug I had the same errors > again on the machines with 3.7.2 after a few days (after other > INSERTS/UPDATES). > > About the thing I should test: > > There is no command called ".export" it seems?! > > But I think that I don't even have to test the thing you propose, as it > will work almost for sure => like already stated several times one > little tiny tiny tiny change already fixes the error. As an > .export/.import will change some bits for sure this will already change > the situation! > > Am 16.11.2010 13:53, schrieb Black, Michael (IS): >> I thought of another test you should try. >> >> Do an .export of your original database using 3.6.23.1 and .import it >> (constructing a new database). Then try your backup. >> >> If that works then you're just seeing corruption in the original database >> that 3.6.23.1 handles (since it created it). >> >> If it doesn't work import into 3.7.3 and test backup again. >> >> If it doesn't work then try cutting the SQL in half until it does work. >> Maybe you'll finally get a small enough size you can post. >> >> >> >> Michael D. Black >> Senior Scientist >> Advanced Analytics Directorate >> Northrop Grumman Information Systems >> >> >> >> >> From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert >> Sent: Tue 11/16/2010 6:27 AM >> To: sqlite-users@sqlite.org >> Subject: EXTERNAL:Re: [sqlite] Strange Corruption >> >> >> >> No, this is definitely not the reason in my case as I can reproduce this >> issue on every 3.7.2/3.7.3 machine I've tested after copying the >> database file (and only the database file) to these machines. >> >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Selective update of a column
Hi all, I can't work out how to do something I feel ought to be easy. Attached small file is designed to be .read to provide a test case. Explanation of what I'm trying to follows below. I have two separate but very similar source data sets, one for mobile one for fixed calls of various sub-types. My target table has a column in (defined as cost REAL) which I want to update based on the values in the sources. Target has a field in which allows me to distinguish mobile rows from fixed call rows, plus a field which has the call sub-types in. The approach I tried was to perform 2 sequential updates using first the fixed call source table then the mobile. I tried to restrict the update to rows in target where the class of call (fixed or mobile) corresponded to the content of the source. What seems to happen is that the second update blats the updates performed by the first: I can have either fixed results or mobile results but not both. I'm pretty sure I'm doing something wrong but haven't been able to find examples which show me how to resolve. Any pointers much appreciated... Regards, Amit Version 3.7.3 on Windows XP [For the time being I have split the target into two and done the individual updates and reassembled. Works - but can't quite believe it's the most elegant solution] /* Update one table with values from another sources 1 & 2 represent fixed and mobile datasets. table target contains mixed data */ drop table if exists source1; create table source1 ( Aend TEXT, type TEXT, cost REAL ); INSERT INTO source1 VALUES('ALF','TFD',1.0); INSERT INTO source1 VALUES('ALF','DTF',2.1); INSERT INTO source1 VALUES('ALF','CP',0.5); INSERT INTO source1 VALUES('ALF','ITF',0.4); INSERT INTO source1 VALUES('ALF','UIFN',0.760); INSERT INTO source1 VALUES('ALF','SC',-1.0); drop table if exists source2; create table source2 ( Aend TEXT, type TEXT, cost REAL ); INSERT INTO source2 VALUES('ALF','TFD',21.0); INSERT INTO source2 VALUES('ALF','DTF',22.1); INSERT INTO source2 VALUES('ALF','CP',20.5); INSERT INTO source2 VALUES('ALF','ITF',20.4); INSERT INTO source2 VALUES('ALF','UIFN',20.760); drop table if exists target; create table target ( Aend TEXT, type TEXT, FM TEXT, cost REAL ); INSERT INTO target VALUES('ALF','TFD','Mobile',0.0); INSERT INTO target VALUES('ALF','UIFN','Mobile',0.0); INSERT INTO target VALUES('ALF','DFT','Mobile',0.0); INSERT INTO target VALUES('ALF','CP','Mobile',0.0); INSERT INTO target VALUES('ALF','SC','Mobile',0.0); INSERT INTO target VALUES('ALF','UIFN','Fixed',0.0); INSERT INTO target VALUES('ALF','CP','Fixed',0.0); INSERT INTO target VALUES('ALF','SC','Fixed',0.0); INSERT INTO target VALUES('ALF','TFD','Fixed',0.0); /* Syntax to update one table with values from another */ update target set cost = (select cost from source1 where source1.Aend=target.Aend and source1.type=target.type and FM='Fixed'); select * from target; select "Split"; update target set cost = (select cost from source2 where source2.Aend=target.Aend and source2.type=target.type and FM='Mobile'); /* Highlight that the data in the cost column after the first update has been over-written by the second which was not what was required. Implies that the only way to make the update process work (as opposed to using other joining techniques) is to partition the data set into separate fixed / mobile tables, perform separate updates and then recombine the updated data. */ select * from target; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selective update of a column
On 16 Nov 2010, at 3:29pm, Amit Chaudhuri wrote: > The approach I tried was to perform 2 sequential updates using first the > fixed call source table then the mobile. I tried to restrict the update to > rows in target where the class of call (fixed or mobile) corresponded to the > content of the source. > > What seems to happen is that the second update blats the updates performed > by the first: I can have either fixed results or mobile results but not > both. You /nearly/ got it right. Your commands were update target set cost = (select cost from source1 where source1.Aend=target.Aend and source1.type=target.type and FM='Fixed'); update target set cost = (select cost from source2 where source2.Aend=target.Aend and source2.type=target.type and FM='Mobile'); Your problem is that you have the clause about FM in the wrong part. The FM column is in the table you're updating, not in the source. So the clause about FM should be part of the UPDATE command, not part of the SELECT. Your code should look more like UPDATE target SET cost = (SELECT cost FROM source1 WHERE source1.Aend=target.Aend AND source1.type=target.type) WHERE FM='Fixed'; UPDATE target SET cost = (SELECT cost FROM source2 WHERE source2.Aend=target.Aend AND source2.type=target.type) WHERE FM='Mobile'; I haven't tried the above code but I hope it might point you in the right direction. To make things fast, don't forget to index your source* tables on (Aend,type) or something like that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Cannot get bind to work
I am trying to familiarize myself with the lower-level routines for queries, so started with the example here: http://www.sqlite.org/quickstart.html ...which uses sqlite3_exec() and I replaced that with prepare/bind/step. When there are no bind parameters in the sql statement, the revised program works - but even putting just one place-holder ('?') and trying to bind at position 1, I get a SQLITE_RANGE error. I have no clue what I'm doing wrong, if any one can help. I am taking the liberty of including the program in-line since it's less then 100 lines, if that's ok. Invocation: working: ./client test.sqlite "select * from emp where ename = 'fred'" not working - error 25, range erorr: ./client test.sqlite "select * from emp where ename = '?'" fred Thanks, -Chris #include #include #include #include void print_row(const char *aColData[], const int nNumCols) { int i; for(i=0; i [bind-param1, bind-param2...]\n", argv[0]); exit(1); } if((rc = sqlite3_open_v2(argv[1], &db, SQLITE_OPEN_READONLY, 0)) != SQLITE_OK) { fprintf(stderr, "%d: Can't open database: %s\n", sqlite3_errmsg(db), __LINE__); sqlite3_close(db); exit(1); } printf("%s\n", argv[2]); if((rc = sqlite3_prepare_v2(db, argv[2], -1, &pStmt, &pzTail)) != SQLITE_OK) { fprintf(stderr, "%d: SQL error: %d - %s\n", rc, sqlite3_errmsg(db), __LINE__); exit(1); } if (argc > 3) { int j; for(j=0; jhttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot get bind to work
On Wed, Nov 17, 2010 at 10:20:22AM -0500, Chris Wolf scratched on the wall: > I am trying to familiarize myself with the lower-level routines for > queries, so started with the > example here: > not working - error 25, range erorr: > > ./client test.sqlite "select * from emp where ename = '?'" fred This is not a place-holder. This is a single-character string-literal that consists of a question mark. Lose the quotes. The quotes are part of the string-literal specification, not the value itself. They're not needed for place-holders. Consider this statement if you were binding a integer, or something other than a text value. -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] Cannot get bind to work
On 17 November 2010 15:27, Jay A. Kreibich wrote: > On Wed, Nov 17, 2010 at 10:20:22AM -0500, Chris Wolf scratched on the wall: >> I am trying to familiarize myself with the lower-level routines for >> queries, so started with the >> example here: > > >> not working - error 25, range erorr: >> >> ./client test.sqlite "select * from emp where ename = '?'" fred > > This is not a place-holder. This is a single-character > string-literal that consists of a question mark. > > Lose the quotes. The quotes are part of the string-literal > specification, not the value itself. They're not needed for > place-holders. Consider this statement if you were binding > a integer, or something other than a text value. > > -j > Furthermore, in your binding, loop from 0 to argc-3 rather than argc for(j=0; 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 Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot get bind to work
Jay A. Kreibich wrote: > On Wed, Nov 17, 2010 at 10:20:22AM -0500, Chris Wolf scratched on the wall: > >> I am trying to familiarize myself with the lower-level routines for >> queries, so started with the >> example here: >> > > > >> not working - error 25, range erorr: >> >> ./client test.sqlite "select * from emp where ename = '?'" fred >> > > This is not a place-holder. This is a single-character > string-literal that consists of a question mark. > > Lose the quotes. The quotes are part of the string-literal > specification, not the value itself. They're not needed for > place-holders. Consider this statement if you were binding > a integer, or something other than a text value. > >-j > that solved it, thanks so much. -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot get bind to work
Simon Davies wrote: > On 17 November 2010 15:27, Jay A. Kreibich wrote: > >> On Wed, Nov 17, 2010 at 10:20:22AM -0500, Chris Wolf scratched on the wall: >> >>> I am trying to familiarize myself with the lower-level routines for >>> queries, so started with the >>> example here: >>> >> >>> not working - error 25, range erorr: >>> >>> ./client test.sqlite "select * from emp where ename = '?'" fred >>> >> This is not a place-holder. This is a single-character >> string-literal that consists of a question mark. >> >> Lose the quotes. The quotes are part of the string-literal >> specification, not the value itself. They're not needed for >> place-holders. Consider this statement if you were binding >> a integer, or something other than a text value. >> >> -j >> >> > > Furthermore, in your binding, loop from 0 to argc-3 rather than argc > >for(j=0; j // for(j=0; j printf("%d: %s\n", j+1, argv[3+j]); > if((rc = sqlite3_bind_text(pStmt, j+1, argv[3+j], -1,SQLITE_TRANSIENT)) >!= SQLITE_OK) { >fprintf(stderr, "%d: SQL error: %d - %s\n", __LINE__, > rc,sqlite3_errmsg(db)); >exit(1); > } >} > > Thanks for that - I also fixed the fprintf(stderr...) where the __LINE__ args were in the wrong position. -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] A Bug? HAVE_LOCALTIME_S=0 under WinCE causes compile error
In my WinCE SDK, HAVE_LOCALTIME_S is defined to 1 This causes a compile error since the headers and libraries don't seem to have localtime_s() I then #define HAVE_LOCALTIME_S 0 but another compile error pops up. This time the error is because localtime() is used at line 12970 before it is actually defined at line 29714. I am using SQLite 3.7.3 amalgamation with VS2008 and VS2005. Regards, Afriza N. Arief ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users