Re: [sqlite] CTE question...

[Keith Medcalf]

Your result data does not appear in the input data.  How do you explain that?

Are you trying to perform a query and simply add a fictitious "logical row 
number" (ie, a result row number) for some reason to the results of a the 
rather simple query:

SELECT DISTINCT code_key FROM _misc_log ORDER BY code_key DESC;

Why cannot your application merely compute this for itself (ie, the first step 
returns row 1, the next row 2, and so on and so forth).  Why do you want to add 
a logical row number to the results?

Relational Databases are based on Relational Algebra.  There is no such thing 
as a "Positional Row Number" in a result set -- nor can I think of any reason 
that you would want one.

---
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 Brian Curley
>Sent: Friday, 15 September, 2017 16:55
>To: SQLite mailing list
>Subject: [sqlite] CTE question...
>
>Admittedly it's a bit of a hack and there may be alternate approaches
>to
>some of it, but I am curious if there's an issue within the WITH
>behavior.
>It could just my approach.
>
>DDL for my base table:
>
>CREATE TABLE _misc_log (
>
>   CODE_KEY  NOT NULL,
>
>   CODE_VAL  NOT NULL,
>
>   ATTRIB,
>
>   PRIMARY KEY (
>
>  CODE_KEY,
>
>  CODE_VAL
>
>   )
>
>);
>
>
>This _misc_log is an ad hoc logging table, where I throw a time-
>stamp,
>table name, and a record count, respectively. I am just storing
>strings in
>the code_* fields, so it can really be anything. The purpose is
>produce a
>quick listing of the unique values in the code_key field, alongside
>of a
>proper row number. (The native result of rowid from the table is not
>useful, since they are typically hundreds of rows apart.)
>
>Sample values:
>
>code_key
>20170914-1200
>20170914-1822
>20170915-0855
>20170915-1718
>
>
>I can get the recursion to work properly to give me the row sequence
>I
>need, but it spins a little wide on sub-query content. Rather than
>give me
>a nice listing of the recursive row number and the code_key string,
>it
>works only for the first two lines...and then spirals into what
>appears to
>be a Cartesian.
>
>I'd expected:
>
>my_row code_key
>
>1 20170915-1718
>2 20170915-0855
>3 20170915-1822
>4 20170915-1200
>5 ...
>
>
>I got:
>
>my_row code_key
>
>1 20170915-1718
>2 20170915-0855
>3 20170915-0855
>4 20170915-0855
>5 20170915-0855
>
>
>CTE used:
>
>   -- WITH   cte -- Name your "logical"
>table.
>   -- AS (SELECT 1 AS n  -- Initialize your
>counter
>value, plus any other columns...
>   -- UNION ALL  --   (UNION rules...)
>   -- SELECT n + 1   -- ...then recursively
>loop
>through the rest of the logical table...
>   -- FROM   cte -- ...using subqueries
>to
>provide for content columns
>   -- WHERE  n < 50  -- ...up to the point of
>the
>max of n, or any other conditionals.
>   --)   --
>   -- SELECT n   -- Then SELECT...
>   -- FROM   cte -- ...from the logical
>table.
>   -- [ LIMIT x ]-- [Alternatively, or
>additionally, limit output as needed.]
>   -- ;  --
>
>--CREATE VIEW vw_my_row_logid AS
>WITH   cte_name  --(my_row, code_key)
>AS (
>   SELECT-- Base record
>1my_row
>  ,(SELECT
>   max(code_key)
>FROM
>   _misc_log
>)log_id
>   UNION ALL
>   SELECT-- Recursion records
>my_row + 1
>  ,(SELECT DISTINCT
>   code_key
>FROM
>   _misc_log
>WHERE
>   code_key
>  !=   (SELECT
>   max(code_key)
>FROM
>   _misc_log )
>ORDER BY 1 desc
>)
>   FROM
> cte_name
>--   WHERE my_row <= 5
>   )
>   -- Resultset here...
>SELECT   *
>FROM cte_name
>LIMIT5
>;
>===
>
>
>Any thoughts on this?
>
>(The internal WHERE and the external LIMIT are redundant.)
>
>​​
>Regards.
>
>Brian P Curley
>___
>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] sqlite CLI scripts using ".echo on" with ".once" or ".output"

[Donald Griggs]

I imagine this is expected behavior but wanted to be sure.

In an sqlite commandline utility (CLI) script, if one enables

.echo on
  for debugging, and redirects output either by

.once filename
   or
.output filename

then the sql commands themselves are included in the output file.

I've taken to putting a defensive ".echo off" just before any .output or
.once
commands to guard against my own absent-mindedness.

Notes:

   1. I've included a simple cut-paste  script below followed by its output.
   2. I tested with
  - Win 7,  and SQLite version 3.20.0
  - Win 10 and SQLite versions 3.20.1 and  3.17.0
  - Ubuntu 15.10 and sqlite 3.8.11.1
   3. I used .mode csv below, but the mode does not appear to matter.
   4. I used ".output stdout" and an in-memory db for illustration, but
   sending the output to a true file or using persistent db doesn't appear to
   matter.
   5. Using a batch file and a file containing the sql (vs cut-paste
   commands) does not appear to matter.
   6. An explicit ".trace off" does not appear to matter.
   7. Interestingly, dot commands entered while .output is in effect are
NOT echoed to the output file as actual sql commands are.


Thanks for a great product,
Donald Griggs
Columbia SC USA

===Paste following to console
sqlite3 :memory:

CREATE TABLE fruits( name TEXT, cnt INTEGER);
INSERT INTO fruits VALUES ('apple', 1), ('pear',2 ), ('mango', 4),
('grape', 3);

SELECT * FROM fruits;

.mode csv

.echo on
.once stdout
SELECT * FROM fruits;

.echo off
.once stdout
SELECT * FROM fruits;
.quit
==

Result (Win 7, with notation added)
==
SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
sqlite>
sqlite> CREATE TABLE fruits( name TEXT, cnt INTEGER);
sqlite> INSERT INTO fruits VALUES ('apple', 1), ('pear',2 ), ('mango', 4),
('grape', 3);
sqlite>
sqlite> SELECT * FROM fruits;
apple|1
pear|2
mango|4
grape|3
sqlite>
sqlite> .mode csv
sqlite>
sqlite> .echo on
sqlite> .once stdout
.once stdout
sqlite> SELECT * FROM fruits;
SELECT * FROM fruits;**The select is included in the
output file
apple,1
pear,2
mango,4
grape,3
sqlite>

sqlite> .echo off
.echo off
sqlite> .once stdout
sqlite> SELECT * FROM fruits;
apple,1
pear,2
mango,4
grape,3
sqlite> .quit
==
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CTE question...

[petern]

Try running your subquery by itself and see if the first row's code_key is
what you want for every column of your recursive outer query:

SELECT DISTINCT
   code_key
FROM
   _misc_log
WHERE
   code_key
  !=   (SELECT
   max(code_key)
FROM
   _misc_log )
ORDER BY 1 desc



On Fri, Sep 15, 2017 at 3:54 PM, Brian Curley <bpcur...@gmail.com> wrote:

> Admittedly it's a bit of a hack and there may be alternate approaches to
> some of it, but I am curious if there's an issue within the WITH behavior.
> It could just my approach.
>
> DDL for my base table:
>
> CREATE TABLE _misc_log (
>
>CODE_KEY  NOT NULL,
>
>CODE_VAL  NOT NULL,
>
>ATTRIB,
>
>PRIMARY KEY (
>
>   CODE_KEY,
>
>   CODE_VAL
>
>)
>
> );
>
>
> This _misc_log is an ad hoc logging table, where I throw a time-stamp,
> table name, and a record count, respectively. I am just storing strings in
> the code_* fields, so it can really be anything. The purpose is produce a
> quick listing of the unique values in the code_key field, alongside of a
> proper row number. (The native result of rowid from the table is not
> useful, since they are typically hundreds of rows apart.)
>
> Sample values:
>
> code_key
> 20170914-1200
> 20170914-1822
> 20170915-0855
> 20170915-1718
>
>
> I can get the recursion to work properly to give me the row sequence I
> need, but it spins a little wide on sub-query content. Rather than give me
> a nice listing of the recursive row number and the code_key string, it
> works only for the first two lines...and then spirals into what appears to
> be a Cartesian.
>
> I'd expected:
>
> my_row code_key
>
> 1 20170915-1718
> 2 20170915-0855
> 3 20170915-1822
> 4 20170915-1200
> 5 ...
>
>
> I got:
>
> my_row code_key
>
> 1 20170915-1718
> 2 20170915-0855
> 3 20170915-0855
> 4 20170915-0855
> 5 20170915-0855
>
>
> CTE used:
>
>-- WITH   cte -- Name your "logical" table.
>-- AS (SELECT 1 AS n  -- Initialize your counter
> value, plus any other columns...
>-- UNION ALL  --   (UNION rules...)
>-- SELECT n + 1   -- ...then recursively loop
> through the rest of the logical table...
>-- FROM   cte -- ...using subqueries to
> provide for content columns
>-- WHERE  n < 50  -- ...up to the point of the
> max of n, or any other conditionals.
>--)   --
>-- SELECT n   -- Then SELECT...
>-- FROM   cte -- ...from the logical table.
>-- [ LIMIT x ]-- [Alternatively, or
> additionally, limit output as needed.]
>-- ;  --
>
> --CREATE VIEW vw_my_row_logid AS
> WITH   cte_name  --(my_row, code_key)
> AS (
>SELECT-- Base record
> 1my_row
>   ,(SELECT
>max(code_key)
> FROM
>_misc_log
> )log_id
>UNION ALL
>SELECT-- Recursion records
> my_row + 1
>   ,(SELECT DISTINCT
>code_key
> FROM
>_misc_log
> WHERE
>code_key
>   !=   (SELECT
>max(code_key)
> FROM
>_misc_log )
> ORDER BY 1 desc
> )
>FROM
>  cte_name
> --   WHERE my_row <= 5
>)
>-- Resultset here...
> SELECT   *
> FROM cte_name
> LIMIT5
> ;
> ===
>
>
> Any thoughts on this?
>
> (The internal WHERE and the external LIMIT are redundant.)
>
> ​​
> Regards.
>
> Brian P Curley
> ___
> 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] CTE question...

[Brian Curley]

Admittedly it's a bit of a hack and there may be alternate approaches to
some of it, but I am curious if there's an issue within the WITH behavior.
It could just my approach.

DDL for my base table:

CREATE TABLE _misc_log (

   CODE_KEY  NOT NULL,

   CODE_VAL  NOT NULL,

   ATTRIB,

   PRIMARY KEY (

  CODE_KEY,

  CODE_VAL

   )

);


This _misc_log is an ad hoc logging table, where I throw a time-stamp,
table name, and a record count, respectively. I am just storing strings in
the code_* fields, so it can really be anything. The purpose is produce a
quick listing of the unique values in the code_key field, alongside of a
proper row number. (The native result of rowid from the table is not
useful, since they are typically hundreds of rows apart.)

Sample values:

code_key
20170914-1200
20170914-1822
20170915-0855
20170915-1718


I can get the recursion to work properly to give me the row sequence I
need, but it spins a little wide on sub-query content. Rather than give me
a nice listing of the recursive row number and the code_key string, it
works only for the first two lines...and then spirals into what appears to
be a Cartesian.

I'd expected:

my_row code_key

1 20170915-1718
2 20170915-0855
3 20170915-1822
4 20170915-1200
5 ...


I got:

my_row code_key

1 20170915-1718
2 20170915-0855
3 20170915-0855
4 20170915-0855
5 20170915-0855


CTE used:

   -- WITH   cte -- Name your "logical" table.
   -- AS (SELECT 1 AS n  -- Initialize your counter
value, plus any other columns...
   -- UNION ALL  --   (UNION rules...)
   -- SELECT n + 1   -- ...then recursively loop
through the rest of the logical table...
   -- FROM   cte -- ...using subqueries to
provide for content columns
   -- WHERE  n < 50  -- ...up to the point of the
max of n, or any other conditionals.
   --)   --
   -- SELECT n   -- Then SELECT...
   -- FROM   cte -- ...from the logical table.
   -- [ LIMIT x ]-- [Alternatively, or
additionally, limit output as needed.]
   -- ;  --

--CREATE VIEW vw_my_row_logid AS
WITH   cte_name  --(my_row, code_key)
AS (
   SELECT-- Base record
1my_row
  ,(SELECT
   max(code_key)
FROM
   _misc_log
)log_id
   UNION ALL
   SELECT-- Recursion records
my_row + 1
  ,(SELECT DISTINCT
   code_key
FROM
   _misc_log
WHERE
   code_key
  !=   (SELECT
   max(code_key)
FROM
   _misc_log )
ORDER BY 1 desc
)
   FROM
 cte_name
--   WHERE my_row <= 5
   )
   -- Resultset here...
SELECT   *
FROM cte_name
LIMIT5
;
===


Any thoughts on this?

(The internal WHERE and the external LIMIT are redundant.)

​​
Regards.

Brian P Curley
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 15 Sep 2017, at 4:38am, Fahad  wrote:

> How can I prevent this from happening? Like I said, using a unit test I was
> able to reproduce this 1 out of 20 tries but when using the real app and
> plugin at the same time, I am able to reproduce every 5th try (it seems the
> app is opening / using / closing connections in a peculiar way).

Was your unit test also using the Safari Plugin architecture ?

Have you every managed to reproduce the fault in a stand-alone program ?  It 
doesn’t need to be your complete App, just a simple test program which opens 
the file and does the INSERT.

Everything you write makes me think your problem is with the Safari Plugin, or 
the Safari architecture in general.  If that’s the case you may need to take 
the problem up with Apple’s developer forum, or using one of your free Apple 
Developer Membership support calls.

Simon.
-- 
 http://www.bigfraud.org | I'd expect if a computer was involved
 | it all would have been much worse.
 No Buffy for you.   |-- John "West" McKenna
 Leave quickly now. -- Anya  |  THE FRENCH WAS THERE

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

[Keith Medcalf]

On Thursday, 14 September, 2017 19:05, Jens Alfke , wrote:

>> On Sep 14, 2017, at 1:23 PM, Keith Medcalf 
>wrote:

>> You merely need to ONCE it either for each input row or for each
>result row.  So for example:

>> select slow(a.x), slow(a.x)*slow(b.y), slow(b.y) from a, b where
>a.this == b.that

>> when computing the result set you merely compute ONCE slow(a.x) and
>ONCE slow(b,y)

>Interesting … I assume ONCE is something internal to the SQLite query
>engine? I don't see any reference to it in the SQL syntax or the
>other docs.

>Is there any way to achieve this effect without modifying SQLite?

Yes, it is an internal thing in the VDBE code.  It is currently used so that 
certain things can be done only ONCE per execution of the prepared statement 
(that is, to generate certain constants that once created do not change 
throughout the entire execution (from the first step until reset), but will be 
regenerated if the statement is reset then run again)).  It basically an if 
type construct for example:

once r5 {
  r5 = (do something)
}

So the first time it is run, r5 is unintialized something is done to compute a 
value stored in r5.  On the next step the value of r5 already exists so the 
computation steps are skipped.

Of course, in this case the calculation is "per step" and you are just ignoring 
duplicate calculations, so it is similar but not really the same thing.

This type of optimization would at least prevent multiple executions of the 
function with the same arguments *in the same step* but would not prevent 
execution with the same arguments in different steps.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

[Clemens Ladisch]

Nico Williams wrote:
> I would much prefer to be able to specify which CTEs must be materialized,
> and which may be left as internal views.  That would give the user a great
> deal of control.  WITH x AS () MATERIALIZED ... .

"Materialized" is the wrong word; you want to prevent only subquery
flattening, but not implementing the subquery as a coroutine.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

[nomad]

On Fri Sep 15, 2017 at 09:55:40AM +0200, Dominique Devienne wrote:
> On Thu, Sep 14, 2017 at 11:43 PM, Nico Williams 
> wrote:
> 
> > [...] I would much prefer to be able to specify which CTEs must be
> > materialized,
> > and which may be left as internal views.  That would give the user a great
> > deal of control.  WITH x AS () MATERIALIZED ... .  Can we get that?
> >
> 
> +1 --DD

I would also like to see such a feature. I don't know if the syntax
above is standard SQL. If not, I would suggest an alternative position
for the extra keyword:

WITH MATERIALIZED
x
AS
( SELECT ... )
SELECT
a
FROM
x

That fits in better with the existing RECURSIVE keyword position, looks
better to my eyes, and would make my life as the author of an SQLite
wrapper easier :-)

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Figuring out the Cause of SQLite Error 11: Corrupt Database

[Richard Hipp]

On 9/14/17, Esplin, Justin  wrote:
>
> We are seeing a couple worrisome (and not very informative) messages
> repeatedly in our logs:
>
> SQLite error (11): database corruption at line 55472 of
> [cf538e2783]
>
> SQLite error (11): database corruption at line 55514 of [cf538e2783]
>
> I have been looking around the web for a few days to try and figure out how
> to diagnose this, but have come up empty-handed. This is occurring on
> various Windows machines, though they appear to functional normally before,
> during and after these errors are written to the logs. It was by chance that
> we stumbled across these errors in the logs.
>
> Is there a way to know what the lines are that the error messages are
> complaining about? Is there a chance that the corruption is of a nominal
> variety or that it is able to automatically recover, which is why it appears
> to continue to function normally?

Both errors are badly formatted b-tree pages in the database file.
These both indicate serious database corruption.  You should be able
to verify the corruption by running "PRAGMA quick_check".

Is the database file located on a network filesystem?
-- 
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] 'database disk image is malformed' only on the mac

[Fahad]

Hi Simon

I tried it with sqlite3_shutdown(), didn't help. I was able to reproduce it
again using the main app and the safari share plugin.

Main App: Finished writing to db, just opened a new connection to read
(after opening a connection I set PRAGMA query_only=1; PRAGMA
read_uncommitted=1; and register some custom functions).

The connection opened fine however the moment the app ran a SELECT statement
I think it coincided with a write from the the Plugin (separate process; it
opens a connection, writes and then closes it), boom the -shm file vanished
and only the .db and .db-wal files were present in the folder. The -wal was
zero bytes.

I have a feeling the -shm file got deleted by the plugin while it was in
fact in use by the main app (the main app only releases all its connections
when closing, so at any given time it has at least one open connection, even
when not being used). 

How can I prevent this from happening? Like I said, using a unit test I was
able to reproduce this 1 out of 20 tries but when using the real app and
plugin at the same time, I am able to reproduce every 5th try (it seems the
app is opening / using / closing connections in a peculiar way).



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Figuring out the Cause of SQLite Error 11: Corrupt Database

[Esplin, Justin]

Howdy,


We are seeing a couple worrisome (and not very informative) messages repeatedly 
in our logs:

SQLite error (11): database corruption at line 55472 of [cf538e2783]

SQLite error (11): database corruption at line 55514 of [cf538e2783]

I have been looking around the web for a few days to try and figure out how to 
diagnose this, but have come up empty-handed. This is occurring on various 
Windows machines, though they appear to functional normally before, during and 
after these errors are written to the logs. It was by chance that we stumbled 
across these errors in the logs.

Is there a way to know what the lines are that the error messages are 
complaining about? Is there a chance that the corruption is of a nominal 
variety or that it is able to automatically recover, which is why it appears to 
continue to function normally?

I also did my best to post a question on SO which you can see 
here.

Thanks in advance for any help you may have to offer.



Justin Esplin
Applications Dev II | Xactware

Phone: +1.801.932.8039

[Verisk Insurance Solutions]

[Forbes 2016 Most Innovative 
Companies][America's
 Best Mid-Size 
Employers][Great
 Place to Work]

verisk.com/xactware | 
vCard | 
Map
 | Email

[Blog][LinkedIn][Twitter]





This email is intended solely for the recipient. It may contain privileged, 
proprietary or confidential information or material. If you are not the 
intended recipient, please delete this email and any attachments and notify the 
sender of the error.



Xactware's opt-in mailing list allows you to receive Xactware News that is of 
interest to you. Visit my.xactware.com today to join or to update your email 
preferences!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

[Dominique Devienne]

On Thu, Sep 14, 2017 at 11:43 PM, Nico Williams 
wrote:

> On Thu, Sep 14, 2017 at 1:10 PM Simon Slavin  wrote:
> > Can you not do it with WITH ?  I don’t really understand how WITH works
> > but it would seem to evaluate its terms just once for each iteration.
>


> [...] I would much prefer to be able to specify which CTEs must be
> materialized,
> and which may be left as internal views.  That would give the user a great
> deal of control.  WITH x AS () MATERIALIZED ... .  Can we get that?
>

+1 --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table-valued functions as PIVOT

[petern]

Richard is right.  Unless you're discarding transformed rowsets, your
pivots will become stale.

If you need a live pivot, I recently disclosed a SQLite native language
"stored procedure"  for computing live pivot views on this mailing list:

http://sqlite.1065341.n5.nabble.com/Generalized-SQLite-stored-procedure-style-pivot-table-exhibit-td95724.html

Further to your exact requirement, you would simply feed into the
generate_pivot procedure the group_concat() aggregate instead of sum().


On Thu, Sep 14, 2017 at 9:37 AM, Gary Briggs  wrote:

> It's been a while since anyone has brought up PIVOT, and none that I
> can see since table-valued functions were added [in 3.9.0, I think?]
>
> Has anyone successfully implemented PIVOT using table-valued functions?
>
> I'm in the situation again where I'd like a better way to pivot, where
> knowledge of possible values in a column can be delayed to execution-time
> rather than needing to be known at query-writing time.
>
> A notional example [which is missing considerate of other columns,
> which might really be this idea's downfall] might look like:
>
> SELECT * FROM person;
> name   | age
> 
> Alice  | 42
> Bob| 27
> Eve| 16
>
> CREATE TABLE pers_piv(person, name);
> SELECT * FROM pers_piv; -- Alice, Bob, Eve, conveniently hidden by *
>
> Alice | Bob | Eve
> -
> 42| 27  | 16
>
>
> Cheers,
> Gary
> ___
> 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] [EXTERNAL] Re: Table-valued functions as PIVOT

[Hick Gunter]

Should not the result (for a simple pivot) be more like

Field | Alice | Bob | Eve
 -
age   | 42| 27  | 16

with one row for each column of the original table?

Or maybe even:

Using pivot (,,);


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Hipp
Gesendet: Donnerstag, 14. September 2017 18:53
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Table-valued functions as PIVOT

On 9/14/17, Gary Briggs  wrote:
> It's been a while since anyone has brought up PIVOT, and none that I
> can see since table-valued functions were added [in 3.9.0, I think?]
>
> Has anyone successfully implemented PIVOT using table-valued functions?
>
> I'm in the situation again where I'd like a better way to pivot, where
> knowledge of possible values in a column can be delayed to
> execution-time rather than needing to be known at query-writing time.
>
> A notional example [which is missing considerate of other columns,
> which might really be this idea's downfall] might look like:
>
> SELECT * FROM person;
> name   | age
> 
> Alice  | 42
> Bob| 27
> Eve| 16
>
> CREATE TABLE pers_piv(person, name);

The syntax would need to be:

   CREATE VIRTUAL TABLE pers_piv USING pivot(person,name);

The resulting pers_piv table would be a pivot of the person table for a 
snapshot in time.  In other words, pers_piv would not track subsequent changes 
in the original person table.

Implementing a pivot virtual table such as described above would not be too 
hard.  I encourage you to give it a try and publish the result.


> SELECT * FROM pers_piv; -- Alice, Bob, Eve, conveniently hidden by *
>
> Alice | Bob | Eve
> -
> 42| 27  | 16
>
>
> Cheers,
> Gary
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users