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;
year apples bananas mangos peaches
---------- ---------- ---------- ---------- ----------
2016 30 4
2017 14 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 <pasm...@concepts.nl> 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
