Re: [sqlite] Question about: Adding a record to a table with select failure

One approach might be something like this: INSERT INTO t (a, b, c, d, e, idate) SELECT 'p006', Coalesce(b, 1), Coalesce(c, 2), 'y', Coalesce(e, 4), '2019-20-12' FROM (SELECT 1) LEFT JOIN (SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate DESC

Re: [sqlite] Adding a record to a table with one value change

Why not like this? insert into t (a, b, c, d, e, idate) SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY idate desc limit 1; On Fri, Nov 15, 2019 at 9:19 AM Simon Slavin wrote: > > On 14 Nov 2019, at 10:06pm, Jose Isaias Cabrera wrote: > > > insert into t (a, b, c, d, e,

Re: [sqlite] Import XLS file?

You might like to consider the xlsx virtual table I wrote last year: https://github.com/jakethaw/xlsx_vtab Note that this only works for xlsx files, and may have unexpected results in some circumstances (e.g. merged cells). On Wed, Oct 2, 2019 at 4:39 AM Winfried wrote: > > Hello, > > I need

[sqlite] Bug: WINDOW clause within a CTE

The following examples demonstrate a possible bug when using a WINDOW clause within a CTE: SQLite version 3.30.0 2019-09-14 16:44:51 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE x AS

Re: [sqlite] "unable to use function highlight in the requested context" in group by

Dan Kennedy explained why this limitation exists: >On 27/2/62 05:47, Jake Thaw wrote: >>This may not strictly be a bug, but currently (3.27.2) a vtab cannot overload >>scalar functions in aggregate queries. >> >>Adding a check for TK_AGG_COLUMN in sqlite3VtabOverloadFu

Re: [sqlite] Is it possible to transpose a table using SQL?

Hi Shane, You might be interested in trying out my pivot virtual table implementation. https://github.com/jakethaw/pivot_vtab This will cater for changing values, but like a VIEW implementation, it does not satisfy your criteria of dynamically changing rows/columns. Changes to rows/columns can

[sqlite] Vtab scalar function overloading in aggregate queries

This may not strictly be a bug, but currently (3.27.2) a vtab cannot overload scalar functions in aggregate queries. Adding a check for TK_AGG_COLUMN in sqlite3VtabOverloadFunction makes my use case function as expected. -Jake ___ sqlite-users mailing

Re: [sqlite] Variable Declaration

There does exist an experimental branch which introduces the concept of shell variables. https://www.sqlite.org/src/timeline?r=shell-bindings The check-in comment describes the usage: "Add the ability to use bind parameters in the CLI. The new ".set KEY=VALUE" dot-command works to set bindings.

Re: [sqlite] SQLite 3.26.0 recursive CTE performance regression

Hi Sebastian, You can achieve better performance by constructing the path as you walk the tree. e.g. WITH tree( id, depth, path ) AS ( SELECT id, 1, id FROM languoid WHERE parent_id IS NULL UNION ALL SELECT l.id, t.depth+1, t.path || '/' || l.id FROM tree

[sqlite] Bug: Incorrect fsdir result

The following returns an erroneous fsdir error. Tested on macOS 10.13.6. SQLite version 3.25.3 2018-11-05 20:37:38 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE d AS SELECT '.' d; sqlite>

[sqlite] Draft Geopoly docs

The following typos exist in the draft Geopoly docs: 3.9 "new polygon that is a affine transformation" -> "new polygon that is an affine transformation" 4.1 "each dimension of each coordinate is of 32-byte floating point number" -> "each dimension of each coordinate is a 32-bit floating point

[sqlite] Bug in shell_bindings bindvtabUpdate

Hi SQLite dev team, I have been testing the shell-bindings branch, and encountered an issue when setting text/blob values via the shell_bindings virtual table. bindvtabUpdate fails to set p->len. Adding p->len = len resolves the issue. Regards Jake

[sqlite] Bug in fsdir

The following query causes a crash in fsdirNext on Windows 10. SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT name FROM fsdir('.') JOIN (VALUES(1),(2)); .

Re: [sqlite] New pre-release snapshot with performance enhancements

Hi Dan, I can confirm that the current snapshot works for me as expected. I was linking against sqlite-snapshot-201701170010, which predates the session enhancement. Thank you for your time. -Jake ___ sqlite-users mailing list

Re: [sqlite] New pre-release snapshot with performance enhancements

Hello Richard, Is the following enhancement included as part of this pre-release snapshot? - Enhance the session extension to support WITHOUT ROWID tables. The draft documentation still says that this support does not yet exist, and my test below also demonstrates this. Please let me know if I

[sqlite] Efficient relational SELECT

Hi Simon, I do this type of query all the time to avoid sub queries and aggregation. This might be what you are looking for to satisfy the elegance criteria: SELECT r.room_id, b.date FROM roomr LEFT JOIN booking b ON r.room_id = b.room_id LEFT JOIN booking b2 ON