Re: [sqlite] "Common Table Expression"

2013-12-26 Thread David de Regt
Sorry, this struck a bit of a sore spot with me, so I apologize for the small rant... Feel free to completely ignore it. CTEs are important for two reasons: 1. Simplification of query syntax. One can argue that this isn't terribly important in a system designed as an embedded database, rather

Re: [sqlite] "Common Table Expression"

2013-12-26 Thread RSmith
This reminds me of a plan to add RADAR dishes to cars to monitor other traffic and avoid collisions - brilliant idea but the detrimental effect on aerodynamics and limiting size-factor of already-built garages all over the world stifled enthusiasm. Probably "Temporary Views" would be the exact

Re: [sqlite] "Common Table Expression"

2013-12-26 Thread Simon Slavin
On 26 Dec 2013, at 7:23pm, big stone wrote: > '1' CTE can be replaced by the creation of 'N' temporary views (or > tables), and their deletion after the CTE request. Just a quick clarification that a VIEW does not greatly increase the amount of data stored in a database.

Re: [sqlite] "Common Table Expression"

2013-12-26 Thread big stone
Hi again, Indeed, '1' CTE can be replaced by the creation of 'N' temporary views (or tables), and their deletion after the CTE request. CTE is : - a syntaxic simplification : . the SQL creator don't have to care about those intermediate views, . these intermediate views don't grow and

Re: [sqlite] "Common Table Expression"

2013-12-26 Thread Simon Slavin
On 26 Dec 2013, at 6:57pm, big stone wrote: > "sub-select" method : > - allows you to do only a part of what you can do in CTE, > - becomes more and more difficult to read as you add tables and treatment > in your sql. > > With CTE in SQLite, it would be possible to: > -

Re: [sqlite] "Common Table Expression"

2013-12-26 Thread big stone
Hi again, "sub-select" method : - allows you to do only a part of what you can do in CTE, - becomes more and more difficult to read as you add tables and treatment in your sql. With CTE in SQLite, it would be possible to: - decompose your SQL treatment in clean intermediate steps, - make your

Re: [sqlite] "Common Table Expression"

2013-12-26 Thread Simon Slavin
On 26 Dec 2013, at 3:27pm, big stone wrote: > Does SQLite plan to implement soon a "Common Table Expression" subset ? Common Table Expressions are implemented as sub-SELECTs, as documented in the 'select-stmt' part of this page:

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

2013-12-26 Thread dean gwilliam
Again, thank you for your advice. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Common Table Expression"

2013-12-26 Thread big stone
Hello, Does SQLite plan to implement soon a "Common Table Expression" subset ? CTE would allow to write much more readable SQLite 'SQL' code. It doesn't seem complex to do, as long as the 'RECURSIVE' part of CTE is ignored. Regards, ___ sqlite-users

Re: [sqlite] Issues after upgrade to 3.8.2

2013-12-26 Thread Richard Hipp
On Wed, Dec 25, 2013 at 3:55 PM, Denis Gladkikh wrote: > Assertion failed! > > Program: ...Windows\GoogleMusic\bin\x86\Debug\AppX\sqlite3.DLL > File: sqlite3.c > Line: 79461 > > Expression: pExpr->iTable==0 || pExpr->iTable==1 > > Program:

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

2013-12-26 Thread Kees Nuyt
On Thu, 26 Dec 2013 11:59:10 +, dean gwilliam wrote: >Thank you all for your helpful advice. An alternative is PRAGMA table_info(yourtable); http://sqlite.org/pragma.html#pragma_table_info It resturns a result set, one row per column in yourtable. -- Groet,

[sqlite] Issues after upgrade to 3.8.2

2013-12-26 Thread Denis Gladkikh
Hi All, After upgrade from 3.8.1 to 3.8.2 I started to get asserts in debug build and {"Attempted to read or write protected memory. This is often an indication that other memory is corrupt."} in Relase when I have x86 app on amd64 Windows 8.1. These are asserts: ---

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

2013-12-26 Thread dean gwilliam
Thank you all for your helpful advice. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

2013-12-26 Thread RSmith
This is actually awesome to know, thanks Stephen, I always thought at least 1 step is needed - I'm going to immediately implement this in some functions! On 2013/12/26 13:30, Stephan Beal wrote: There are probably a few approaches that would work, but I can think of none quicker/more

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

2013-12-26 Thread Stephan Beal
On Thu, Dec 26, 2013 at 12:21 PM, RSmith wrote: > There are probably a few approaches that would work, but I can think of > none quicker/more efficient than maintaining a list of column names > ("SELECT * from t WHERE 1 LIMIT 1" will produce it real quick) If you want JUST

Re: [sqlite] select only records with fields that BEGIN with a certain sub-string

2013-12-26 Thread Petite Abeille
On Dec 26, 2013, at 11:49 AM, dean gwilliam wrote: > ...is this possible or should I return the whole lot and subject it to a > regexp filter to get my cut-down list? http://www.sqlite.org/lang_expr.html#like ___

[sqlite] select only records with fields that BEGIN with a certain sub-string

2013-12-26 Thread dean gwilliam
...is this possible or should I return the whole lot and subject it to a regexp filter to get my cut-down list? Any advice much appreciated and BTWmerry Christmas to you all! ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Programming in SQL vs. a procedural computer language

2013-12-26 Thread Klaas V
Tobias wrote "... simpler just to have afterwards some things to do in my program? " What is 'simple' is an off topic discussion. More important is the reason of making changes by you and/or your colleagues. I've seen coding in a procedural language I could only figure out by reading the