[sqlite] Searching this mailing lsit
RMtrix writes: > > Mail-Archive.com returns no hits for 2013 and 2014 even when the search term is "SQLite". None until > sometime in 2015. So the archive is incomplete or its index is corrupted. > > On the other site, your search has results only in 2013 and 2016. With Mail-Archive.com having no data for > 2013, it returns results only from 2016. > > Tom > > > On May 20, 2016, at 9:19 PM, Kasajian, Kenneth > wrote: > > > > When I attempt to search the archives of this mailing list for the word: LSM_CONFIG_AUTOFLUSH > > Using http://www.mail-archive.com/sqlite- users%40mailinglists.sqlite.org/ > > I get no hits. > Mail-Archive.com has two archives for the sqlite-users mailing list because its address changed on Feb 13, 2015 from sqlite- users.sqlite.org:8080 to sqlite-users.mailinglists.sqlite.org The two archives are, respectively, https://www.mail-archive.com/sqlite-users at sqlite.org/ https://www.mail-archive.com/sqlite-users at mailinglists.sqlite.org/ I asked if they could be combined and the answer was "Give us a few days and we'll let you know when they're combined." Tom
[sqlite] Searching this mailing lsit
> Mail-Archive.com returns no hits for 2013 and 2014 even when the search term > is "SQLite". None until sometime in 2015. So the archive is incomplete or its > index is corrupted. > > On the other site, your search has results only in 2013 and 2016. With > Mail-Archive.com having no data for 2013, it returns results only from 2016. > > Tom > >> On May 20, 2016, at 9:19 PM, Kasajian, Kenneth > schneider-electric.com> wrote: >> >> When I attempt to search the archives of this mailing list for the word: >> LSM_CONFIG_AUTOFLUSH >> Using http://www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/ >> I get no hits.
[sqlite] COLLATE NU800_NOCASE as a loadable extension?
Aleksey Tulinov writes: . > > Unfortunately i'm not familiar with SQLite Expert, however > > >2.6 Message : no such collation sequence:_RMNOCASE > > Collation provided by nunicode SQLite extension is called NU800_NOCASE > (or NU800 for case-sensitive collation), that name is need to be used if > you want nunicode to handle "COLLATE" expressions. The OP wants a revision of your loadable extension with the collation name NU800_NOCASE renamed or also named RMNOCASE, to be used with a database created by a software that has a flawed collation so named. I would like to try it, too. Tom
Re: [sqlite] WHERE expression with operators from text functions?
FTS MATCH was a great solution to that particular problem. Thanks again, Dr. Hipp! Back to original question... Is it at all possible to pass the results of a SELECT to a WHERE expression? I have used a SELECT to create a full statement but then I have to copy the result to a query editor and execute it. Tom > On Oct 16, 2014, at 12:55 PM, Tom Holden wrote: > > I think you are right. The FTS MATCH looks like it should function the way > I want. > > Thanks, Richard! I will now learn how to use FTS... > > Tom > >> On Thu, Oct 16, 2014 at 12:20 PM, Richard Hipp wrote: >> >>> On Thu, Oct 16, 2014 at 12:03 PM, Tom Holden wrote: >>> >>> Coming up with a subject was a struggle and maybe that indicates an >>> impossibility. Searching the archive was equally fruitless. >>> >> >> Perhaps what you really want is Full Text Search. >> http://www.sqlite.org/fts3.html >> >> >>> >>> What I am trying to do is to build a SELECT with a compound WHERE using >>> one or more run-time parameters. Sort of like transforming an input >> phrase >>> such as: >>> "string1+string2+string3..." >>> INTO >>> WHERE >>> [Value] LIKE '%string1%' >>> AND >>> [Value] LIKE '%string2%' >>> AND >>> [Value] LIKE '%string3%' >>> AND... >>> >>> This brute force method works: >>> WHERE >>> [Value] LIKE '%'||$SearchString_ONE||'%' >>> AND >>> [Value] LIKE '%'||$SearchString_TWO||'%' >>> ... >>> but requires every parameter to be acted on (filled in or made blank). >>> >>> I can build a statement that produces a desirable looking expression but >>> cannot evaluate it as such with WHERE: >>> >>> SELECT '[Value] LIKE '||'''%'||REPLACE($SearchString, '+', '%'''||' AND >>> [Value] LIKE '||'''%')||'%''' >>> produces >>> [Value] LIKE '%string1%' AND [Value] LIKE '%string2%' AND [Value] LIKE >>> '%string3%' >>> >>> but >>> >>> WHERE (above SELECT...) evaluates to FALSE >>> >>> I need a way to convert the text result to an expression that WHERE >>> evaluates as an expression. >>> >>> Any possibility to do this within SQLite? >>> >>> Tom >>> ___ >>> 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 >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE expression with operators from text functions?
I think you are right. The FTS MATCH looks like it should function the way I want. Thanks, Richard! I will now learn how to use FTS... Tom On Thu, Oct 16, 2014 at 12:20 PM, Richard Hipp wrote: > On Thu, Oct 16, 2014 at 12:03 PM, Tom Holden wrote: > > > Coming up with a subject was a struggle and maybe that indicates an > > impossibility. Searching the archive was equally fruitless. > > > > Perhaps what you really want is Full Text Search. > http://www.sqlite.org/fts3.html > > > > > > What I am trying to do is to build a SELECT with a compound WHERE using > > one or more run-time parameters. Sort of like transforming an input > phrase > > such as: > > "string1+string2+string3..." > > INTO > > WHERE > > [Value] LIKE '%string1%' > > AND > > [Value] LIKE '%string2%' > > AND > > [Value] LIKE '%string3%' > > AND... > > > > This brute force method works: > > WHERE > > [Value] LIKE '%'||$SearchString_ONE||'%' > > AND > > [Value] LIKE '%'||$SearchString_TWO||'%' > > ... > > but requires every parameter to be acted on (filled in or made blank). > > > > I can build a statement that produces a desirable looking expression but > > cannot evaluate it as such with WHERE: > > > > SELECT '[Value] LIKE '||'''%'||REPLACE($SearchString, '+', '%'''||' AND > > [Value] LIKE '||'''%')||'%''' > > produces > > [Value] LIKE '%string1%' AND [Value] LIKE '%string2%' AND [Value] LIKE > > '%string3%' > > > > but > > > > WHERE (above SELECT...) evaluates to FALSE > > > > I need a way to convert the text result to an expression that WHERE > > evaluates as an expression. > > > > Any possibility to do this within SQLite? > > > > Tom > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WHERE expression with operators from text functions?
Coming up with a subject was a struggle and maybe that indicates an impossibility. Searching the archive was equally fruitless. What I am trying to do is to build a SELECT with a compound WHERE using one or more run-time parameters. Sort of like transforming an input phrase such as: "string1+string2+string3..." INTO WHERE [Value] LIKE '%string1%' AND [Value] LIKE '%string2%' AND [Value] LIKE '%string3%' AND... This brute force method works: WHERE [Value] LIKE '%'||$SearchString_ONE||'%' AND [Value] LIKE '%'||$SearchString_TWO||'%' ... but requires every parameter to be acted on (filled in or made blank). I can build a statement that produces a desirable looking expression but cannot evaluate it as such with WHERE: SELECT '[Value] LIKE '||'''%'||REPLACE($SearchString, '+', '%'''||' AND [Value] LIKE '||'''%')||'%''' produces [Value] LIKE '%string1%' AND [Value] LIKE '%string2%' AND [Value] LIKE '%string3%' but WHERE (above SELECT...) evaluates to FALSE I need a way to convert the text result to an expression that WHERE evaluates as an expression. Any possibility to do this within SQLite? Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] False Error 'no such collation sequence'
> Date: Fri, 21 Jun 2013 21:23:20 +0300 > From: Bogdan Ureche > To: General Discussion of SQLite Database > Subject: Re: [sqlite] False Error 'no such collation sequence' > > You are mistaken. The free version of SQLite Expert supports loadable > extensions. > > Bogdan Ureche > author of SQLite Expert Me bad. I found the option to turn on extension support in the menu. And thanks to Jean-Christophe Deschamps, I now have a variant of the unifuzz.dll extension which does what I want not only in your SQLite Expert Personal but also in the command-line shell sqlite3.exe. Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] False Error 'no such collation sequence'
Richard Hipp drh at sqlite.org Fri Jun 21 00:15:56 EDT 2013 wrote: Can you not compile your custom collation sequences into a loadable extension (a DLL or shared library), then load that extension into your SQLite shell? http://www.sqlite.org/loadext.html - Tom replies: With Ralf's help, that's what I have done with SQLiteSpy in 2011 after it started to support loadable extensions. While I see that SQLite3.exe supports loadable extensions, I am not a programmer in C and barely a novice in MS Visual Studio C#. The fake collation DLL for SQLiteSpy won't load into SQLite3.exe; it was compiled in Delphi. Having a loadable extension for the custom collation sequence SQLite3.exe would get around the problem with some modification in batch scripts to cause it to load but would leave the SQL scripts as is. It would open up other uses for the command line shell to modify the custom-collated data. That would be good. If someone is willing to give me an extension for SQLite3.exe that gives the name RMNOCASE to a NOCASE equivalent collation sequence, that would be wonderful! I wonder, though, if there may still remain a problem for other shells (SQLite managers?) that do not support loadable extensions, e.g., free versions of SQLite Expert, SQLite Developer, ... Thanks for your responses, Richard. Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] False Error 'no such collation sequence'
Ralf Junker ralfjunker at gmx.de Thu Jun 20 18:44:15 EDT 2013 wrote: On 19.06.2013 17:18, Tom Holden wrote: I use the SQLiteSpy compilation of SQLite3 which recently upgraded from pre SQLite 3.7.8 to pre 4.2.0. You must be mistaken. As the author of SQLiteSpy, I can clearly say that there is no SQLiteSpy version 3.7.8 nor 4.2.0. The current version is SQLiteSpy 1.9.3 which implements SQLite 3.7.16.1. --- Tom replies: The versions are those of SQLite3 or of DISQLite3 according to your version history for SQLIteSpy at http://www.yunqa.de/delphi/doku.php/products/sqlitespy/history. I am not sure what you mean by "pre SQLite 3.7.8" for SQLiteSpy 1.9.1 - was it compiled from SQLite 3.7.7? I have Windows command line shell sqlite3.exe versions 3.7.5 and the latest 3.7.17. The older one does not have this problem nor do I recall that SQLiteSpy 1.9.1 did either, although it was with that version that you helped me out with a fake collation in a loadable extension which opened up avenues for modifying data indexed by the missing collation. If SQLiteSpy 1.9.1 did not have the "false" errors and used SQLite 3.7.7 and SQLiteSpy 1.9.3 exhibits the errors and uses SQLite 3.7.16.1, then we can conclude that changes to SQLite3 after 3.7.7 and up to 3.7.16.1 introduced the problem. I agree that the SQLiteSpy 1.9.3 behaviour is the same as that of sqlite3.exe 3.7.17 with respect to these error messages for my examples just as there were no error messages for the same examples with SQLiteSpy 1.9.1 and sqlite3.exe 3.7.5. Other SQLite managers are going to exhibit the same behaviours, depending on their version of SQLite3. Those based on the later, error-inducing SQLite3 are going to be less useful in dealing with databases using proprietary collation sequences unless they support custom collations and a suitable extension is available. I say that because collation override does not work if the query invokes an index of a field using the missing collation. In my SourceTable example with an index on Name collated by the (missing) CUSTOM sequence, the following now fails, whereas it did not before: SELECT Name FROM SourceTable COLLATE NOCASE; -- Richard Hipp drh at sqlite.org Thu Jun 20 10:47:41 EDT 2013 wrote: The use of an index rather than the original table when doing a scan is a feature, not a bug. You can work around it by adding "ORDER BY rowid" to your query. --- Tom replies: I don't see how invoking an index for a table scan can have any benefit. For me, it's an unwelcome "feature" requiring revisions of queries developed over nearly four years for them (and only some of them) to be restored to usability with SQLite managers that do not support custom collations. Given that collation override does not appear to work in cases where it used to, some of those scripts cannot be resurrected. I realise that my usage of SQLite on a database outside of the application that created it with a proprietary collation sequence that is unavailable to my SQLIte manager is uncommon but surely not unique. I hope that it is possible for SQLite3 to return to the tolerance it once had for missing collations without jeopardising any of the gains that it has made in query optimisation. Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] False Error 'no such collation sequence'
(my apology for trying to reply to a message in the archive which has no reply-to feature – I hope it ends up in the right thread) Richard, I think I know why your test of the command line shell returned no error – an index is needed on the field with the missing collation. I am guessing what has changed is that the query optimiser now (unnecessarily?) selects an appropriate index, if one exists, even for a simple SELECT with no ordering or other function desirous of an index. Revising my example: CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE CUSTOM, RefNumber TEXT, ...); CREATE INDEX idxSourceName ON SourceTable (Name); (sorry, I failed to include this) Error not thrown: SELECT RefNumber ... or any combination of fields, INCLUDING the Name field (the only one to have the proprietary collation), AS LONG AS one of the fields is other than the primary key and the Name (I think this more accurately describes the results) “Error: no such collation sequence: CUSTOM”: SELECT Name FROM ... SELECT SourceID FROM ... SELECT SourceID, Name FROM ... -- SELECT Name and any combination of other fields FROM ... (I got that wrong) Further, even with ORDER BY, prior versions used to tolerate collation override SELECT Name COLLATE NOCASE ... FROM table ORDER BY Name The current versions throw the error. A new observation is that: IF a second index exists on a field that uses a non-missing collation, e.g., CREATE INDEX idxRefNumber ON SourceTable(RefNumber); THEN Error not thrown: SELECT SourceID FROM ... i.e., the query optimiser cannot choose between the indexes. I doubt that the use of an index on these simple SELECTs is of any benefit and suspect that it may be an unintended consequence of some other improvement to the query optimiser. Thanks for your attention. Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] False Error 'no such collation sequence'
I use the SQLiteSpy compilation of SQLite3 which recently upgraded from pre SQLite 3.7.8 to pre 4.2.0. I also occasionally use the SQLite3 Windows command-line shell. I am now encountering an error with the current versions that I did not with earlier ones – sorry, I cannot be precise at this time as to when it began. The problem arises with simple SELECTs on a table which has one or more fields defined with an unavailable collation sequence. Previously, the only time an error was thrown would be when such field was ordered or an index was engaged that included it. Now the error is thrown on a simple SELECT of the field or of the PRIMARY KEY, even though the latter is not defined with the missing collation. No error is thrown if the Primary Key is included with other fields that do not use the missing collation. The collation is missing because the database is created by proprietary software. Example: CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE CUSTOM, RefNumber TEXT, ...) Error not thrown: SELECT RefNumber ... or any combination of fields other than the Name field, the only one to have the proprietary collation “Error: no such collation sequence: CUSTOM”: SELECT Name FROM ... SELECT SourceID FROM ... SELECT SourceID, Name FROM ... SELECT Name and any combination of other fields FROM ... Further, even with ORDER BY, prior versions used to tolerate SELECT Name COLLATE NOCASE ... FROM table ORDER BY Name The current versions throw the error. From my perspective, this is a bug that limits the tools available to work with a proprietary database to fewer than there used to be – the command line shell being one rendered incapable. Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query with UNION on large table
-Original Message-
From: Max Vlasov
Sent: Thursday, June 23, 2011 5:09 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query with UNION on large table
On Wed, Jun 22, 2011 at 9:25 PM, Rense Corten wrote:
>
> CREATE TABLE table2 AS SELECT n1,n2 FROM (SELECT n1, n2 FROM table1
> UNION SELECT n2 AS n1, n1 AS n2 FROM table1) WHERE(n1 n1,n2;
>
> This has the desired result on a small example, but when I try this on
> my actual table which has about 800 million rows, the query never
> seems to complete. It has been running for a couple of days now, and
> it doesn't seem sqlite is still doing anything (cpu usage dropped to
> almost zero)...
>
I noticed that fewer reads/writes will be with the following variant
SELECT * FROM (SELECT n1, n2 FROM table1 Where n1 < n2)
UNION
SELECT * FROM (SELECT n2, n1 FROM table1 Where n2 < n1)
since this one decreases the sizes of the tables that should be ordered.
Max Vlasov
___
Both the original and Max's streamlined query produce the wrong results for
what is intended. Revising Max's produces the most efficient one submitted
so far:
SELECT n1, n2 FROM table1 Where n1 < n2
INTERSECT
SELECT n2, n1 FROM table1 Where n2 < n1;
That's essentially the same as what I had earlier proposed but with the
added condition on the first SELECT that reduces the result set in the
temporary B-Tree (I'm getting deeper than I'm comfortable with now but
that's what I surmise from QUERY PLAN...).
My observations are on two datasets of 100,000 records - one that has but
one mirrored pairings in {0-65535}, the other with almost 45,000 mirrored
pairings (45 distinct) in {0-9}. Execution is faster on the second set by a
factor of almost 3.
Tom
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query with UNION on large table
Is the Hard Drive thrashing? Could be that most everything is being done in swap files. Given the size of the table, a lot more RAM would help. Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query with UNION on large table
This is even faster, on a tiny table: SELECT n1, n2 FROM table1 INTERSECT SELECT n2, n1 FROM table1 WHERE n2 1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automating the build of a sqlite database
Perhaps by adding .quit to your schema.sql Tom -Original Message- From: Maurice Marinus Sent: Friday, April 22, 2011 11:18 AM To: sqlite-users@sqlite.org Subject: [sqlite] Automating the build of a sqlite database So how would I get the batch file to run and terminate the sqlite command shell automatically? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request for help with SQLite Query to return missingDate/Time Ranges
Could you not do this: Select A.time + 1 minute, B.time - 1 minute from log as A, log as B where A.RowID=B.RowID+1 and B.time-A.time > 1 minute Tom -Original Message- From: Andrew Lindsay Sent: Wednesday, April 20, 2011 7:34 PM To: sqlite-users@sqlite.org Subject: [sqlite] Request for help with SQLite Query to return missingDate/Time Ranges Dear Group, I am trying to search an SQL database that is meant to have entries logged every minute for a period of approximately 15 months. I want to create a query that will search through the database and tell me for which periods I do not have any entries. Any assistance would be greatly appreciated. Regards Andrew Lindsay ___ 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] Case-sensitivity, performance and LIKE
- Original Message - From: "Jay A. Kreibich" To: "General Discussion of SQLite Database" Sent: Tuesday, March 30, 2010 9:26 PM Subject: Re: [sqlite] Case-sensitivity, performance and LIKE > On Tue, Mar 30, 2010 at 08:41:09PM -0400, Tom Holden scratched on the > wall: >> >> - Original Message - >> From: "Simon Slavin" > >> > columnName TEXT COLLATE NOCASE >> > >> > then all sorting and SELECT queries on it will ignore case. >> >> You don't even have to change the defined collation as you can impose the >> NOCASE collation in the SELECT statement as: >> >> SELECT * from Customers WHERE LastName = 'Shaw' COLLATE NOCASE AND >> FirstName >> = 'Gioia' COLLATE NOCASE; > > If either/both of these columns has/have indexes, it is best to change > it in the table definition. Otherwise you also need to define it in the > index definition, as well as everywhere you expect to use the index. > This is all automatic if everything is built with the collation in > the table definition. I have used this technique to query a database of a commercial application with a proprietary, inaccessible nocase collation sequence and over whose structure I have no control. Seems to work alright but I have no way of assessing what the cost penalty might be over the 'best way'. Besides, it was not for a 'life or death' application (albeit one that tracks lives and deaths). Thought I would throw it in as an option. Maybe it works OK because the custom collation is a superset of the integral NOCASE collation and my data does not lie outside the latter. If some data did lie outside the intersection of the two collations, I suppose there would be some sorting errors if the indexes are left on the superset and the query uses the override with the subset collation. But then there could be data that lies outside the superset that would not be correctly sorted even with the 'best way'. Isn't that the conundrum caused by so many different character sets among the world's many languages? The more universal the collation sequence, the more expensive the index? Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Case-sensitivity, performance and LIKE
- Original Message - From: "Simon Slavin" To: "General Discussion of SQLite Database" Sent: Tuesday, March 30, 2010 8:12 PM Subject: Re: [sqlite] Case-sensitivity, performance and LIKE > > On 31 Mar 2010, at 12:51am, Rashed Iqbal wrote: > >> Is there a way to make SQLite queries case-insensitive? >> >> For example my query is: >> >> SELECT * from Customers WHERE LastName = 'Shaw' AND FirstName = 'Gioia' >> >> and I want to be able to get same results no matter if the case is good >> in the DB or not or in the query. > > You need a change from the standard collating system. Take a look at > section 6.2 of > > http://www.sqlite.org/datatype3.html > > I haven't tried this myself but I think that if you define a column as > > columnName TEXT COLLATE NOCASE > > then all sorting and SELECT queries on it will ignore case. > You don't even have to change the defined collation as you can impose the NOCASE collation in the SELECT statement as: SELECT * from Customers WHERE LastName = 'Shaw' COLLATE NOCASE AND FirstName = 'Gioia' COLLATE NOCASE; Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Passing Value from one query to another
Store the results of the first query in a temporary table?
- Original Message -
From: "personalt"
To:
Sent: Saturday, February 06, 2010 8:54 AM
Subject: Re: [sqlite] Passing Value from one query to another
>
> I realize this works fine for this query but is there a way to to do this
> by
> passing the results from one query to the second? I have some more
> complex
> calculations coming up where I think this would be an easier way to go
>
>
>
> BareFeet wrote:
>>
>> On 06/02/2010, at 10:07 PM, personalt wrote:
>>
>>> I am just looking to pass a value from one query into a second. Is
>>> that possible? Can I modify the query below to get the two queries to
>>> work
>>> together so that the .19 is repalce by the kwhcost1 from the first
>>> query?
>>>
>>> select kwhcost1 from applications;
>>>
>>> SELECT monitordata_hourly.deviceaddress,
>>> Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
>>> Round(Sum(monitordata_hourly.ch1kwh),3)*.19 AS SumOfch1kwh_cost
>>> FROM monitordata_hourly
>>> Where monitordata_hourly.deviceaddress=142265 and
>>> (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
>>> days')
>>> Group by monitordata_hourly.deviceaddress
>>
>> Simply enter your first query as a "subquery" in the second, like this:
>>
>> SELECT monitordata_hourly.deviceaddress,
>> Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
>> Round(Sum(monitordata_hourly.ch1kwh),3)*(select kwhcost1 from
>> applications) AS SumOfch1kwh_cost
>> FROM monitordata_hourly
>> Where monitordata_hourly.deviceaddress=142265 and
>> (datetime(monitordata_hourly.date))>=datetime('now', 'localtime', '-30
>> days')
>> Group by monitordata_hourly.deviceaddress
>>
>> Tom
>> BareFeet
>>
>> --
>> Comparison of SQLite GUI tools:
>> http://www.tandb.com.au/sqlite/compare/?ml
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context:
> http://old.nabble.com/Passing-Value-from-one-query-to-another-tp27478736p27479816.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] update on Ticket 3437
Nick, I think your query fails because there is a potential for multiple values on the right-hand side of WHERE RaceID=. Try this: WHERE RaceId = (SELECT DISTINCT ID FROM Races WHERE Name = 'totley moor' AND Date LIKE "2009%"); For that matter, wouldn't your query be better composed as: SELECT * from races, times WHERE RACEID = ID and Races.Name = 'totley moor' AND Date LIKE "2009%"; Tom - Original Message - From: To: Sent: Saturday, January 23, 2010 7:59 PM Subject: [sqlite] update on Ticket 3437 > Hi, > I submitted this some time ago, see > http://www.sqlite.org/cvstrac/tktview?tn=3437,39. > > I've just been messing about with this again and have found out what was > happening. The following script demonstrates the problem and resolution: > [begin bug2.sql] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline
The point is not how the table was created but rather that the absence of the RMNOCASE collation causes the query to crash the latest versions of sqlite while earlier versions gracefully report an error. Moreover, having saved a VIEW from this query resulted in these managers of later releases of sqlite (e.g. 3.6.21/22) reporting the access violation on opening the database. Go back far enough, to, say 3.5.4, and the query runs with no problem. I think that may have been where the VIEW was created. So what is a working query and VIEW in 3.5.4, became syntactically an error by 3.6.17 and a crash by 3.6.21. Tom - Original Message - From: "Pavel Ivanov" To: "General Discussion of SQLite Database" Sent: Thursday, January 21, 2010 9:36 AM Subject: Re: [sqlite] SQL Crash with sqlite 3.6.22 commandline > I am unable to reproduce this problem. Using the script below, with > RMNOCASE changed to just NOCASE Probably that's exactly the point of crash in the OP's test case. He created table when RMNOCASE collation existed but then tries to execute query when that collation is not registered and unknown. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Speed regression after 3.6.17
I have been trying out a number of SQLite managers, one test being the execution time for the same query on the same database on the same computer. The scattering of results was very surprising with a spread on the order of 10:1. I followed up with one of the developers and he was able to identify the cause and give me a patch to accelerate his product. What he found was that those competing products that executed the fastest were using an old version of SQLite while the slowest ones used the latest. He was able to identify a speed regression occurring at 3.6.18. His patch was to replace my sqlite3.dll from 3.6.22 with one from 3.6.17. I confirmed the improvement in speed. The query involves the UNION ALL of 5 SELECTs of 4 tables with INNER JOINS plus a self-JOIN on 2 of the SELECTs and an ORDER BY on 1 field. Regards, Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
