Re: [sqlite] Simple SQL question?
Thanks, they both work indeed. Nice work! This bit looks strange to me: SELECT GetAgeAtDate(dob, issue_date) FROM table_p as issue_date is not in table_p. Never realised that this could work and learned something there. RBS On Wed, Nov 15, 2017 at 10:12 PM, Keith Medcalf wrote: > > Then try making the where clause explicitly qualified with the table names. > > UPDATE table_a >SET issue_date = (SELECT GetAgeAtDate(dob, issue_date) >FROM table_p > WHERE table_p.id = table_a.id); > > Actually, that would be correct. You can use the query of the form > > UPDATE table_a >SET issue_date = (SELECT GetAgeAtDate(dob, issue_date) >FROM table_p > WHERE id = table_a.id); > > as unqualified duplicate named columns in the correlated subquery are > preferentially assumed to refer to tables mentioned in the from clause of > the correlated subquery. > > Duplicated column names that you wish to refer to the outer correlated > table must be qualified. Non-duplicate column names will be found where > they exist, either in the inner or outer correlate. > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert > >Sent: Wednesday, 15 November, 2017 15:05 > >To: SQLite mailing list > >Subject: Re: [sqlite] Simple SQL question? > > > >They end up in the wrong row. > > > >RBS > > > >On Wed, Nov 15, 2017 at 9:59 PM, Keith Medcalf > >wrote: > > > >> > >> That is not possible since there is only one column called > >issue_date in > >> all the tables mentioned in the query ... > >> > >> > >> --- > >> The fact that there's a Highway to Hell but only a Stairway to > >Heaven says > >> a lot about anticipated traffic volume. > >> > >> > >> >-Original Message- > >> >From: sqlite-users [mailto:sqlite-users- > >> >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert > >> >Sent: Wednesday, 15 November, 2017 14:55 > >> >To: SQLite mailing list > >> >Subject: Re: [sqlite] Simple SQL question? > >> > > >> >That is getting close, but the calculated values end up with the > >> >right ID, > >> >but the wrong ISSUE_DATE. > >> >Will if an order by can sort this out. > >> > > >> >RBS > >> > > >> >On Wed, Nov 15, 2017 at 9:33 PM, David Raymond > >> > > >> >wrote: > >> > > >> >> Try... > >> >> > >> >> UPDATE TABLE_A SET AGE_AT_ISSUE = > >> >> (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM > >> >> TABLE_P AS P WHERE P.ID = ID); > >> >> > >> >> > >> >> -Original Message- > >> >> From: sqlite-users [mailto:sqlite-users- > >> >boun...@mailinglists.sqlite.org] > >> >> On Behalf Of Bart Smissaert > >> >> Sent: Wednesday, November 15, 2017 4:17 PM > >> >> To: General Discussion of SQLite Database > >> >> Subject: [sqlite] Simple SQL question? > >> >> > >> >> Have 2 tables, TABLE_A and TABLE_P like this: > >> >> > >> >> CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE > >> >INTEGER) > >> >> > >> >> CREATE TABLE_P(ID INTEGER, DOB INTEGER) > >> >> > >> >> ID is he common field. > >> >> > >> >> Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it > >will > >> >hold the > >> >> age of the person identified by ID, at the date of ISSUE_DATE. > >> >> I do this with a UDF, taking 2 arguments, in this case DOB (date > >of > >> >birth) > >> >> and ISSUE_DATE. > >> >> The UDF is not relevant in this example and it could as well be > >> >ISSUE_DATE > >> >> - DOB. > >> >> > >> >> I tried this: > >> >> > >> >> UPDATE TABLE_A SET AGE_AT_ISSUE = > >> >> (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A > >> >> INNER JOIN TABLE_P P ON(A.ID = P.ID) > >> >> > >> >> But that will make the column AGE_AT_ISSUE have the same value > >for > >> >all > >> >> rows, which is he first row of the select. > >> >> > >> >> The select by itself will give the right values. > >> >> > >> >> Any suggestions how this can be done with just one statement? > >> >> > >> >> > >> >> RBS > >> >> ___ > >> >> sqlite-users mailing list > >> >> sqlite-users@mailinglists.sqlite.org > >> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- > >> >users > >> >> ___ > >> >> sqlite-users mailing list > >> >> sqlite-users@mailinglists.sqlite.org > >> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- > >> >users > >> >> > >> >___ > >> >sqlite-users mailing list > >> >sqlite-users@mailinglists.sqlite.org > >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- > >users > >> > >> > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailm
Re: [sqlite] Simple SQL question?
Then try making the where clause explicitly qualified with the table names. UPDATE table_a SET issue_date = (SELECT GetAgeAtDate(dob, issue_date) FROM table_p WHERE table_p.id = table_a.id); Actually, that would be correct. You can use the query of the form UPDATE table_a SET issue_date = (SELECT GetAgeAtDate(dob, issue_date) FROM table_p WHERE id = table_a.id); as unqualified duplicate named columns in the correlated subquery are preferentially assumed to refer to tables mentioned in the from clause of the correlated subquery. Duplicated column names that you wish to refer to the outer correlated table must be qualified. Non-duplicate column names will be found where they exist, either in the inner or outer correlate. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert >Sent: Wednesday, 15 November, 2017 15:05 >To: SQLite mailing list >Subject: Re: [sqlite] Simple SQL question? > >They end up in the wrong row. > >RBS > >On Wed, Nov 15, 2017 at 9:59 PM, Keith Medcalf >wrote: > >> >> That is not possible since there is only one column called >issue_date in >> all the tables mentioned in the query ... >> >> >> --- >> The fact that there's a Highway to Hell but only a Stairway to >Heaven says >> a lot about anticipated traffic volume. >> >> >> >-Original Message- >> >From: sqlite-users [mailto:sqlite-users- >> >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert >> >Sent: Wednesday, 15 November, 2017 14:55 >> >To: SQLite mailing list >> >Subject: Re: [sqlite] Simple SQL question? >> > >> >That is getting close, but the calculated values end up with the >> >right ID, >> >but the wrong ISSUE_DATE. >> >Will if an order by can sort this out. >> > >> >RBS >> > >> >On Wed, Nov 15, 2017 at 9:33 PM, David Raymond >> > >> >wrote: >> > >> >> Try... >> >> >> >> UPDATE TABLE_A SET AGE_AT_ISSUE = >> >> (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM >> >> TABLE_P AS P WHERE P.ID = ID); >> >> >> >> >> >> -Original Message- >> >> From: sqlite-users [mailto:sqlite-users- >> >boun...@mailinglists.sqlite.org] >> >> On Behalf Of Bart Smissaert >> >> Sent: Wednesday, November 15, 2017 4:17 PM >> >> To: General Discussion of SQLite Database >> >> Subject: [sqlite] Simple SQL question? >> >> >> >> Have 2 tables, TABLE_A and TABLE_P like this: >> >> >> >> CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE >> >INTEGER) >> >> >> >> CREATE TABLE_P(ID INTEGER, DOB INTEGER) >> >> >> >> ID is he common field. >> >> >> >> Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it >will >> >hold the >> >> age of the person identified by ID, at the date of ISSUE_DATE. >> >> I do this with a UDF, taking 2 arguments, in this case DOB (date >of >> >birth) >> >> and ISSUE_DATE. >> >> The UDF is not relevant in this example and it could as well be >> >ISSUE_DATE >> >> - DOB. >> >> >> >> I tried this: >> >> >> >> UPDATE TABLE_A SET AGE_AT_ISSUE = >> >> (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A >> >> INNER JOIN TABLE_P P ON(A.ID = P.ID) >> >> >> >> But that will make the column AGE_AT_ISSUE have the same value >for >> >all >> >> rows, which is he first row of the select. >> >> >> >> The select by itself will give the right values. >> >> >> >> Any suggestions how this can be done with just one statement? >> >> >> >> >> >> RBS >> >> ___ >> >> sqlite-users mailing list >> >> sqlite-users@mailinglists.sqlite.org >> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >> >users >> >> ___ >> >> sqlite-users mailing list >> >> sqlite-users@mailinglists.sqlite.org >> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >> >users >> >> >> >___ >> >sqlite-users mailing list >> >sqlite-users@mailinglists.sqlite.org >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users >> >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running sums and averages
Memory is cheap and most servers have plenty. Processors are fast and most servers have multiple with many cores. Select the entire table of columns you need into memory. Write a little code. No it won't scale very well into millions of rows but I could easily run a test and I will bet many mnay many dollars that processing the sums in memory is orders of magnitude faster than SQL. You shouldn't even need to read the entire table (or view) into memory: just read row-by-row, and for each field, keep a running total and the count of non-NULL values. From these you can calculate your total and both types of average. Graham yes .. that is even better ! Dennis ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
They end up in the wrong row. RBS On Wed, Nov 15, 2017 at 9:59 PM, Keith Medcalf wrote: > > That is not possible since there is only one column called issue_date in > all the tables mentioned in the query ... > > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > >-Original Message- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert > >Sent: Wednesday, 15 November, 2017 14:55 > >To: SQLite mailing list > >Subject: Re: [sqlite] Simple SQL question? > > > >That is getting close, but the calculated values end up with the > >right ID, > >but the wrong ISSUE_DATE. > >Will if an order by can sort this out. > > > >RBS > > > >On Wed, Nov 15, 2017 at 9:33 PM, David Raymond > > > >wrote: > > > >> Try... > >> > >> UPDATE TABLE_A SET AGE_AT_ISSUE = > >> (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM > >> TABLE_P AS P WHERE P.ID = ID); > >> > >> > >> -Original Message- > >> From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] > >> On Behalf Of Bart Smissaert > >> Sent: Wednesday, November 15, 2017 4:17 PM > >> To: General Discussion of SQLite Database > >> Subject: [sqlite] Simple SQL question? > >> > >> Have 2 tables, TABLE_A and TABLE_P like this: > >> > >> CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE > >INTEGER) > >> > >> CREATE TABLE_P(ID INTEGER, DOB INTEGER) > >> > >> ID is he common field. > >> > >> Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will > >hold the > >> age of the person identified by ID, at the date of ISSUE_DATE. > >> I do this with a UDF, taking 2 arguments, in this case DOB (date of > >birth) > >> and ISSUE_DATE. > >> The UDF is not relevant in this example and it could as well be > >ISSUE_DATE > >> - DOB. > >> > >> I tried this: > >> > >> UPDATE TABLE_A SET AGE_AT_ISSUE = > >> (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A > >> INNER JOIN TABLE_P P ON(A.ID = P.ID) > >> > >> But that will make the column AGE_AT_ISSUE have the same value for > >all > >> rows, which is he first row of the select. > >> > >> The select by itself will give the right values. > >> > >> Any suggestions how this can be done with just one statement? > >> > >> > >> RBS > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- > >users > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- > >users > >> > >___ > >sqlite-users mailing list > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
It is often helpful to study the syntax diagrams to see what is possible and intended by the language: https://sqlite.org/lang_update.html Take a look at the WHERE clause. The WHERE clause determines which rows are UPDATEd. One weakness in the documentation (although it may otherwise generally be known about SQL) is that one may fully qualify column names with table dot prefix. In this case, you can precisely constrain TABLE_A.ID in the WHERE clause of the UPDATE according to your requirements. [FYI: there may be a typo in the suggested answer I saw posted earlier with a WHERE clause. If you want to affect rows of the UPDATEd table, the WHERE clause has to be at the scope of the UPDATE statement, not a subquery.] On Wed, Nov 15, 2017 at 1:16 PM, Bart Smissaert wrote: > Have 2 tables, TABLE_A and TABLE_P like this: > > CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER) > > CREATE TABLE_P(ID INTEGER, DOB INTEGER) > > ID is he common field. > > Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the > age of the person identified by ID, at the date of ISSUE_DATE. > I do this with a UDF, taking 2 arguments, in this case DOB (date of birth) > and ISSUE_DATE. > The UDF is not relevant in this example and it could as well be ISSUE_DATE > - DOB. > > I tried this: > > UPDATE TABLE_A SET AGE_AT_ISSUE = > (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A > INNER JOIN TABLE_P P ON(A.ID = P.ID) > > But that will make the column AGE_AT_ISSUE have the same value for all > rows, which is he first row of the select. > > The select by itself will give the right values. > > Any suggestions how this can be done with just one statement? > > > RBS > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
That is not possible since there is only one column called issue_date in all the tables mentioned in the query ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert >Sent: Wednesday, 15 November, 2017 14:55 >To: SQLite mailing list >Subject: Re: [sqlite] Simple SQL question? > >That is getting close, but the calculated values end up with the >right ID, >but the wrong ISSUE_DATE. >Will if an order by can sort this out. > >RBS > >On Wed, Nov 15, 2017 at 9:33 PM, David Raymond > >wrote: > >> Try... >> >> UPDATE TABLE_A SET AGE_AT_ISSUE = >> (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM >> TABLE_P AS P WHERE P.ID = ID); >> >> >> -Original Message- >> From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] >> On Behalf Of Bart Smissaert >> Sent: Wednesday, November 15, 2017 4:17 PM >> To: General Discussion of SQLite Database >> Subject: [sqlite] Simple SQL question? >> >> Have 2 tables, TABLE_A and TABLE_P like this: >> >> CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE >INTEGER) >> >> CREATE TABLE_P(ID INTEGER, DOB INTEGER) >> >> ID is he common field. >> >> Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will >hold the >> age of the person identified by ID, at the date of ISSUE_DATE. >> I do this with a UDF, taking 2 arguments, in this case DOB (date of >birth) >> and ISSUE_DATE. >> The UDF is not relevant in this example and it could as well be >ISSUE_DATE >> - DOB. >> >> I tried this: >> >> UPDATE TABLE_A SET AGE_AT_ISSUE = >> (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A >> INNER JOIN TABLE_P P ON(A.ID = P.ID) >> >> But that will make the column AGE_AT_ISSUE have the same value for >all >> rows, which is he first row of the select. >> >> The select by itself will give the right values. >> >> Any suggestions how this can be done with just one statement? >> >> >> RBS >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users >> >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to develop a GUI front-end
On 15 Nov 2017, at 8:16pm, Balaji Ramanathan wrote: > Thank you very much for all your suggestions. For now, I am going to start > with a windows forms application in vb.net or forms in OpenOffice. You are locking yourself into the Windows system. By all means use this solution as a prototype but if you ever find yourself saying "I’m now doing serious programming." do what you can to escape Windows, Office, and Visual-*. Otherwise you will continue to be a niche programmer with a very small niche, subject to panics every time your users upgrade their versions of Windows and Office. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
That is getting close, but the calculated values end up with the right ID, but the wrong ISSUE_DATE. Will if an order by can sort this out. RBS On Wed, Nov 15, 2017 at 9:33 PM, David Raymond wrote: > Try... > > UPDATE TABLE_A SET AGE_AT_ISSUE = > (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM > TABLE_P AS P WHERE P.ID = ID); > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Wednesday, November 15, 2017 4:17 PM > To: General Discussion of SQLite Database > Subject: [sqlite] Simple SQL question? > > Have 2 tables, TABLE_A and TABLE_P like this: > > CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER) > > CREATE TABLE_P(ID INTEGER, DOB INTEGER) > > ID is he common field. > > Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the > age of the person identified by ID, at the date of ISSUE_DATE. > I do this with a UDF, taking 2 arguments, in this case DOB (date of birth) > and ISSUE_DATE. > The UDF is not relevant in this example and it could as well be ISSUE_DATE > - DOB. > > I tried this: > > UPDATE TABLE_A SET AGE_AT_ISSUE = > (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A > INNER JOIN TABLE_P P ON(A.ID = P.ID) > > But that will make the column AGE_AT_ISSUE have the same value for all > rows, which is he first row of the select. > > The select by itself will give the right values. > > Any suggestions how this can be done with just one statement? > > > RBS > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
UPDATE table_a SET issue_date = (SELECT GetAgeAtDate(dob, issue_date) FROM table_p WHERE table_p.id = id); --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert >Sent: Wednesday, 15 November, 2017 14:17 >To: General Discussion of SQLite Database >Subject: [sqlite] Simple SQL question? > >Have 2 tables, TABLE_A and TABLE_P like this: > >CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER) > >CREATE TABLE_P(ID INTEGER, DOB INTEGER) > >ID is he common field. > >Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will >hold the >age of the person identified by ID, at the date of ISSUE_DATE. >I do this with a UDF, taking 2 arguments, in this case DOB (date of >birth) >and ISSUE_DATE. >The UDF is not relevant in this example and it could as well be >ISSUE_DATE >- DOB. > >I tried this: > >UPDATE TABLE_A SET AGE_AT_ISSUE = >(SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A >INNER JOIN TABLE_P P ON(A.ID = P.ID) > >But that will make the column AGE_AT_ISSUE have the same value for >all >rows, which is he first row of the select. > >The select by itself will give the right values. > >Any suggestions how this can be done with just one statement? > > >RBS >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
Try... UPDATE TABLE_A SET AGE_AT_ISSUE = (SELECT GetAgeAtDate(P.DOB, ISSUE_DATE) FROM TABLE_P AS P WHERE P.ID = ID); -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert Sent: Wednesday, November 15, 2017 4:17 PM To: General Discussion of SQLite Database Subject: [sqlite] Simple SQL question? Have 2 tables, TABLE_A and TABLE_P like this: CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER) CREATE TABLE_P(ID INTEGER, DOB INTEGER) ID is he common field. Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the age of the person identified by ID, at the date of ISSUE_DATE. I do this with a UDF, taking 2 arguments, in this case DOB (date of birth) and ISSUE_DATE. The UDF is not relevant in this example and it could as well be ISSUE_DATE - DOB. I tried this: UPDATE TABLE_A SET AGE_AT_ISSUE = (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A INNER JOIN TABLE_P P ON(A.ID = P.ID) But that will make the column AGE_AT_ISSUE have the same value for all rows, which is he first row of the select. The select by itself will give the right values. Any suggestions how this can be done with just one statement? RBS ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
I like to just update that table as this AGE_AT_ISSUE column will be used often in various statements. There must be a simple way to do this, but just can't figure it out. RBS On Wed, Nov 15, 2017 at 9:25 PM, Peter Da Silva < peter.dasi...@flightaware.com> wrote: > Wouldn’t you create a view instead, and not bother calculating > age_at_issue until necessary since it’s derived completely from two other > columns? > > On 11/15/17, 3:16 PM, "sqlite-users on behalf of Bart Smissaert" < > sqlite-users-boun...@mailinglists.sqlite.org on behalf of > bart.smissa...@gmail.com> wrote: > > Have 2 tables, TABLE_A and TABLE_P like this: > > CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER) > > CREATE TABLE_P(ID INTEGER, DOB INTEGER) > > ID is he common field. > > Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will > hold the > age of the person identified by ID, at the date of ISSUE_DATE. > I do this with a UDF, taking 2 arguments, in this case DOB (date of > birth) > and ISSUE_DATE. > The UDF is not relevant in this example and it could as well be > ISSUE_DATE > - DOB. > > I tried this: > > UPDATE TABLE_A SET AGE_AT_ISSUE = > (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A > INNER JOIN TABLE_P P ON(A.ID = P.ID) > > But that will make the column AGE_AT_ISSUE have the same value for all > rows, which is he first row of the select. > > The select by itself will give the right values. > > Any suggestions how this can be done with just one statement? > > > RBS > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQL question?
Wouldn’t you create a view instead, and not bother calculating age_at_issue until necessary since it’s derived completely from two other columns? On 11/15/17, 3:16 PM, "sqlite-users on behalf of Bart Smissaert" wrote: Have 2 tables, TABLE_A and TABLE_P like this: CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER) CREATE TABLE_P(ID INTEGER, DOB INTEGER) ID is he common field. Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the age of the person identified by ID, at the date of ISSUE_DATE. I do this with a UDF, taking 2 arguments, in this case DOB (date of birth) and ISSUE_DATE. The UDF is not relevant in this example and it could as well be ISSUE_DATE - DOB. I tried this: UPDATE TABLE_A SET AGE_AT_ISSUE = (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A INNER JOIN TABLE_P P ON(A.ID = P.ID) But that will make the column AGE_AT_ISSUE have the same value for all rows, which is he first row of the select. The select by itself will give the right values. Any suggestions how this can be done with just one statement? RBS ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple SQL question?
Have 2 tables, TABLE_A and TABLE_P like this: CREATE TABLE_A(ID INTEGER, ISSUE_DATE INTEGER, AGE_AT ISSUE INTEGER) CREATE TABLE_P(ID INTEGER, DOB INTEGER) ID is he common field. Now I want to update the field AGE_AT_ISSUE of TABLE_A, so it will hold the age of the person identified by ID, at the date of ISSUE_DATE. I do this with a UDF, taking 2 arguments, in this case DOB (date of birth) and ISSUE_DATE. The UDF is not relevant in this example and it could as well be ISSUE_DATE - DOB. I tried this: UPDATE TABLE_A SET AGE_AT_ISSUE = (SELECT GetAgeAtDate(P.DOB, A.ISSUE_DATE) FROM TABLE_A A INNER JOIN TABLE_P P ON(A.ID = P.ID) But that will make the column AGE_AT_ISSUE have the same value for all rows, which is he first row of the select. The select by itself will give the right values. Any suggestions how this can be done with just one statement? RBS ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to develop a GUI front-end
On 11/15/17, Peter Da Silva wrote: > Tk is platform independent, so long as you don’t do UNIX-specific stuff (eg, > assume UNIX file paths and stuff) any Tk app should work just fine on > Windows. A majority of the SQLite source code and also the Fossil SCM source code (those parts written by me, which is the majority in both cases) was composed in a cross-platform text editor that is written in Tcl/Tk, based on the text widget. The same TclTk source file runs on linux, mac, and windows. I've used that same editor for two decades, now and then updating it for this or that new feature that I find useful. SQLite began life as a Tcl extension that only later escaped into the wild. That's why most of the test cases for SQLite are written in Tcl, why "tclsh" is required to build from canonical sources, why the Tcl language bindings are built in, why SQLite accept parameters that look like Tcl language variables, and why SQLite uses flexible typing and does not require explicit data types on columns. There is a new book out on Tcl by Ashok Nadkarni. See http://wiki.tcl.tk/48868 for details. I have not read it myself, but people I trust tell me that it is good. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to develop a GUI front-end
Take a look at wx{Phoenix, Python}. It is much simpler, written in python, supports all its versions, and there demos and samples on its website - www.wxpython.org Thank you. On Wed, Nov 15, 2017 at 2:32 PM, Peter Da Silva wrote: > Tk is platform independent, so long as you don’t do UNIX-specific stuff (eg, > assume UNIX file paths and stuff) any Tk app should work just fine on > Windows. You may need to tweak the fonts, eg: > > if { $tcl_platform(platform) eq "windows" } { set font {Arial} } else { > set font {Helvetica} } > > Have a look at the examples at https://wiki.tcl.tk/4149 > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to develop a GUI front-end
Tk is platform independent, so long as you don’t do UNIX-specific stuff (eg, assume UNIX file paths and stuff) any Tk app should work just fine on Windows. You may need to tweak the fonts, eg: if { $tcl_platform(platform) eq "windows" } { set font {Arial} } else { set font {Helvetica} } Have a look at the examples at https://wiki.tcl.tk/4149 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to develop a GUI front-end
Thank you very much for all your suggestions. For now, I am going to start with a windows forms application in vb.net or forms in OpenOffice. Tcl/Tk is a steeper learning curve, and if someone can point me to some good resources that will walk a beginner through the development of a windows GUI on that platform, I would appreciate it. Thank you again. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] segfault in load-extension but not in app
Hi, I am getting seg fault when same code is run as loadable-extension but not when run as shared lib linked to app. Thanks in advance for all help. The C++ code is compiled to 1. shared library and linked to app 2. loadable-extension and loaded from sqlite command line using '.load'. The C++ code is exactly same in both cases (except extension init section) Sqlite version is 3.21.0. Ubuntu 16.04 LTS 64-bit in virtualbox VM on windows 7 host. GCC 6.1.0 I do not get any issues when the code is run as app. The code runs fine as loadable-extension as well except, it SEG faults during cleanup: Here are two snippets of the app's C++ code where I have observed this: ==Snippet 1 //Drop all views created in sqlite std::vector view_vector; view_vector.push_back("MYDATA"); std::string dropView; for (std::vector ::iterator iter=view_vector.begin(); iter != view_vector.end(); ++iter) { LOG_DEBUG << "Dropping View: " << (*iter) << endl; dropView=("DROP VIEW IF EXISTS "+(*iter)); if (SQLITE_OK != sqlite3_exec(m_pDb, dropView.c_str(), NULL, NULL, NULL)) { LOG_ERROR << "Exec failed for Drop View " << (*iter) << ". Error Code= " << sqlite3_errcode(m_pDb)<< " Error Message= "<< sqlite3_errmsg(m_pDb) << " Line=" << __LINE__ << endl; }; }; ==Snippet 2 //Generic Finalize loop -- requires Sqlite version 3.5+ //NOTE: Since we are finalizing it is correct to pass 0 as second arg all the time. sqlite3_stmt *pStmt; while( (pStmt = sqlite3_next_stmt(m_pDb, 0))!=0 ) { LOG_TRACE << "Finalizing SQL: " << sqlite3_sql(pStmt) <) at malloc.c:4167 #1 0x7766dcde in _int_malloc (av=av@entry=0x779b0b20 , bytes=bytes@entry=1032) at malloc.c:3450 #2 0x77670184 in __GI___libc_malloc (bytes=1032) at malloc.c:2913 #3 0x0042cce3 in sqlite3MemMalloc () #4 0x0041b33a in sqlite3Malloc () #5 0x0041b6a7 in dbMallocRawFinish () #6 0x00427a13 in growOpArray.isra.378 () #7 0x00427a5f in growOp3 () #8 0x00427b54 in sqlite3VdbeCreate () #9 0x0046bef7 in sqlite3DropTable () #10 0x004678ae in sqlite3RunParser () #11 0x0046aef9 in sqlite3Prepare () #12 0x0046b207 in sqlite3LockAndPrepare () #13 0x0046b41d in sqlite3_prepare_v2 () #14 0x0045de9e in sqlite3_exec () #15 0x769032ab in WRAPPER1::~WRAPPER1 (this=0x6d3c40, __in_chrg=) at src/wrapper1_defn.cpp:209 #16 0x76901171 in WRAPPER1::removeInstance () at src/wrapper1_defn.cpp:81 #17 0x76900207 in ss_world::removeInstance (this=0x76dab1a0 ) at src/load_extension.cpp:186 #18 0x768fe2d0 in ss_world::~ss_world (this=0x76dab1a0 , __in_chrg=) at src/load_extension.cpp:28 #19 0x77625ff8 in __run_exit_handlers (status=0, listp=0x779b05f8 <__exit_funcs>, run_list_atexit=run_list_atexit@entry=true) at exit.c:82 #20 0x77626045 in __GI_exit (status=) at exit.c:104 #21 0x7760c837 in __libc_start_main (main=0x404550 , argc=4, argv=0x7fffdb58, init=, fini=, rtld_fini=, stack_end=0x7fffdb48) at ../csu/libc-start.c:325 #22 0x004052b9 in _start () (gdb) == Sometimes I also get SEG fault at: === (gdb) where #0 malloc_consolidate (av=av@entry=0x779b0b20 ) at malloc.c:4167 #1 0x7766c678 in _int_free (av=0x779b0b20 , p=, have_lock=0) at malloc.c:4075 #2 0x7767053c in __GI___libc_free (mem=) at malloc.c:2968 #3 0x00417074 in sqlite3_free () #4 0x00421572 in sqlite3VdbeClearObject () #5 0x00421954 in sqlite3VdbeDelete () #6 0x00454b74 in sqlite3VdbeFinalize () #7 0x00454bde in sqlite3_finalize () #8 0x76889a2a in WRAPPER1::~WRAPPER1 (this=0x7121e0, __in_chrg=) at src/wrapper1_defn.cpp:181 #9 0x76887de5 in WRAPPER1::removeInstance () at src/wrapper1_defn.cpp:81 #10 0x76886e71 in ss_world::removeInstance (this=0x76daa8e0 ) at src/load_extension.cpp:196 #11 0x76884e80 in ss_world::~ss_world (this=0x76daa8e0 , __in_chrg=) at src/load_extension.cpp:30 #12 0x77625ff8 in __run_exit_handlers (status=0, listp=0x779b05f8 <__exit_funcs>, run_list_atexit=run_list_atexit@entry=true) at exit.c:82 #13 0x77626045 in __GI_exit (status=) at exit.c:104 #14 0x7760c837 in __libc_start_main (main=0x404550 , argc=4, argv=0x7fffdb58, init=, fini=, rtld_fini=, stack_end=0x7fffdb48) at ../csu/libc-start.c:325 #15 0x004052b9 in _start () (gdb) === I suspect memory corruption so I ran valgrind. It reports some possible lost blocks during extension loading but none during shared library linked to app. ==Valgrind report with loadable-extension(reports errors)=== ==32189== HEAP SUMMARY: ==32
Re: [sqlite] 1TB limit on encrypted database
On 11/15/17, Andrew Stewart wrote: > Thanks. This has worked. Temporarily. You cannot increase the max_page_count above 2147483646, so if your database continues to grow, you are going to need to look into increasing the page size from 1024 to something larger like 8192. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 1TB limit on encrypted database
Thanks. This has worked. Andrew Stewart Argus Control Systems Ltd. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, November 14, 2017 12:34 PM To: SQLite mailing list Subject: Re: [sqlite] 1TB limit on encrypted database On 11/14/17, Andrew Stewart wrote: > Richard, > If I am trying to change the max_page_count use the SEE.EXE > program, how do I get the value to be updated in the database. Every > time I try changing this and then exit see.exe, the value has not > changed. Do I need the updated encryption extension first? The PRAGMA only changes the one database connection in which it runs. To make the setting persistent, recompile with -DSQLITE_MAX_PAGE_COUNT=20 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to develop a GUI front-end
On 11/14/17, 10:32 PM, "sqlite-users on behalf of J Decker" wrote: > Initially I was interested in tcl/tk, and still am, but I'm not sure about > the tcl/tk packaging that would be necessary to make use on multiple > computers. You can bundle a whole Tcl application in a single executable called a “starkit” along with all the necessary packages and libraries exposed internally as a virtual file system. > Also, I don't know how network database connections would work out, would it > be the same as web based stuff or more complicated, etc. For sqlite you don’t generally make network database connections, it uses a library model. For other databases there’s a number of conventional script interfaces. DBI is popular for cross-database use: https://core.tcl.tk/jenglish/gutter/packages/dbi.html and I’m currently maintainer for the original PostgreSQL-Tcl interface https://wiki.tcl-lang.org/13016 ... ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to develop a GUI front-end
I contacted the Tcl core team and this is the response from Steve Landers: > tcl-lang.org was a temporary measure a few years ago when the .tk DNS went > missing. It wasn’t advertised but I guess it is now. > I’ve fixed it The official site is still at tcl.tk. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why there is duplicated sha1 functions in sqlite3 sources ?
Hello ! I'm making a build of sqlite3 that includes sha1 and shathree and other extensions, then made some changes to makefiles (Makefile.in, main.mk) then when trying to run the tests I got an error "multiple definition of 'SHA1Transform'" when trying to link dbhash.c . Why is sha1 duplicated on dbhash.c instead of reusing the one in ext/misc/sha1.c ? Also I'm still waiting to know why sha1.c sqlite_extension return a hex string but shathree return a binary string ? It seems that some cleanup can be done here ! Cheers ! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LSM1 vtable extension portability
I'm reading https://www.sqlite.org/src/artifact/529255dc70428900, and stumbled on: ** For FLOAT values, the content is the IEEE754 floating point value in ** native byte-order. This means that FLOAT values will be corrupted when ** database file is moved between big-endian and little-endian machines. And my immediate reaction is why? SQLite DBs are portable themselves. Why make the decision to break that portability when using the LSM1 extension? I strongly suspect SQLite4, which also uses LSM, uses a portable encoding for row data (the contrary would be extremely surprising), so why go for non-portability on that one datatype, instead of 1) encoding the endianness in the type, or 2) forcing a given endianness, or 3) using varints to encode the mantissa and exponent, therefore providing the platform portability? Thanks, --DD PS: typo: varaible-length PPS: Is the SQLite4 varint encoding ( https://sqlite.org/src4/doc/trunk/www/varint.wiki) different from the SQLite3 one? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users