Re: [sqlite] Generalized SQLite stored procedure style pivot table exhibit.

[petern]

It just occurred to me that I didn't completely finish my thought in the
earlier reply.
The suggested pivot views synchronization TRIGGER is named
quarterly_sales_fruit_insert instead of simply quarterly_sales_insert for a
specific reason.  There is no need to regenerate pivot views unless the
fruit set changes.

So, the more complete solution is to use the implied monotonic rowid column
to condition view regeneration if and only if a new fruit type was added to
the quarterly_sales table.  See the WHEN clause below for how that works.
[Also, obviously, similar UPDATE and DELETE triggers will also needed if
the sales table must accommodate changes to existing records.]

DROP TRIGGER IF EXISTS quarterly_sales_fruit_insert;
CREATE TRIGGER quarterly_sales_fruit_insert AFTER INSERT ON quarterly_sales
WHEN new.fruit NOT IN (SELECT DISTINCT fruit from quarterly_sales WHERE
rowid wrote:

> 15 mei 2017, 07:34 petern:
>
> Here I revisit the pivot table problem using the SQLite stored procedure
>> pattern and pure unmodified SQLite.  I am seeking feedback for improving
>> the brevity of the stored procedure presented here.
>>
>
> Hi, initially I got: near "eval": syntax error. This is after saving the
> mail message as a text file and taking that in the editor two become an sql
> script. This apparently leaves some two-byte spaces, causing this humanly
> invisable error.
> How to fix this in VI I don't know.
> Downloading the text by simple copy and paste cures fixed the problem.
> The procedure works great and can easily be customized.
> I'd generate a temporary view as one must not be tempted to keep it (and
> miss new fruits).
> Thanks, E. Pasma
>
> ___
> 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] Generalized SQLite stored procedure style pivot table exhibit.

[petern]

Some text presentation software may be sensitive to the necessary quote
escaping regime.  In this case, double single quotes are necessary to
escape interior single quotes of the nested literals.  Perhaps one or both,
mine or yours, email client(s) is producing an undesired transformation of
nearby characters or quotation marks?

About view synchronization. This is practical point I forgot to mention.
Pivot views are conveniently synchronized by a data table trigger.  For
example:

---
DROP TRIGGER IF EXISTS quarterly_sales_fruit_insert;
CREATE TRIGGER quarterly_sales_fruit_insert AFTER INSERT ON quarterly_sales
BEGIN
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','sum');
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','min');
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','max');
  INSERT INTO generate_pivot1_view
VALUES('quarterly_sales','year','fruit','tons','avg');
END;

--Now add 7 tons of banana sales to Q3 of 2017 and see what happens--
INSERT INTO quarterly_sales VALUES(2017,3,'bananas',7);

sqlite> SELECT * FROM pivot_view_quarterly_sales_year_fruit_tons_sum;

yearapples  bananas mangos  peaches
--  --  --  --  --
201630  4
201714  7   12
---

[The quarterly_sales table could itself also be a view.  Then the view
synchronizing trigger could be more precisely applied on a normalized
fruit_type table.]














On Tue, May 16, 2017 at 4:32 PM, E.Pasma  wrote:

> 15 mei 2017, 07:34 petern:
>
> Here I revisit the pivot table problem using the SQLite stored procedure
>> pattern and pure unmodified SQLite.  I am seeking feedback for improving
>> the brevity of the stored procedure presented here.
>>
>
> Hi, initially I got: near "eval": syntax error. This is after saving the
> mail message as a text file and taking that in the editor two become an sql
> script. This apparently leaves some two-byte spaces, causing this humanly
> invisable error.
> How to fix this in VI I don't know.
> Downloading the text by simple copy and paste cures fixed the problem.
> The procedure works great and can easily be customized.
> I'd generate a temporary view as one must not be tempted to keep it (and
> miss new fruits).
> Thanks, E. Pasma
>
> ___
> 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] Generalized SQLite stored procedure style pivot table exhibit.

[J. King]

On May 16, 2017 7:32:41 PM EDT, "E.Pasma"  wrote:
>15 mei 2017, 07:34 petern:
>
>> Here I revisit the pivot table problem using the SQLite stored  
>> procedure
>> pattern and pure unmodified SQLite.  I am seeking feedback for  
>> improving
>> the brevity of the stored procedure presented here.
>
>Hi, initially I got: near "eval": syntax error. This is after saving  
>the mail message as a text file and taking that in the editor two  
>become an sql script. This apparently leaves some two-byte spaces,  
>causing this humanly invisable error.
>How to fix this in VI I don't know.
>Downloading the text by simple copy and paste cures fixed the problem.
>The procedure works great and can easily be customized.
>I'd generate a temporary view as one must not be tempted to keep it  
>(and miss new fruits).
>Thanks, E. Pasma
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

The eval function is provided by an extension:


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

Re: [sqlite] Generalized SQLite stored procedure style pivot table exhibit.

[E.Pasma]

15 mei 2017, 07:34 petern:

Here I revisit the pivot table problem using the SQLite stored  
procedure
pattern and pure unmodified SQLite.  I am seeking feedback for  
improving

the brevity of the stored procedure presented here.


Hi, initially I got: near "eval": syntax error. This is after saving  
the mail message as a text file and taking that in the editor two  
become an sql script. This apparently leaves some two-byte spaces,  
causing this humanly invisable error.

How to fix this in VI I don't know.
Downloading the text by simple copy and paste cures fixed the problem.
The procedure works great and can easily be customized.
I'd generate a temporary view as one must not be tempted to keep it  
(and miss new fruits).

Thanks, E. Pasma

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