On Saturday, 8 July, 2017 19:02, Simon Slavin <slav...@bigfraud.org> wrote:
> On 9 Jul 2017, at 1:44am, Keith Medcalf <kmedc...@dessus.com> wrote:
> > I can't visualize what you mean. Something like:
> > create view constants (col1, col2) as values (1,1), (1,2);
>
> > seems the most straightforward to me, and allows you to assign column
> > names to the data.
> But that’s not SQL syntax, is it ? (Read that in a puzzled voice. I
> genuinely don’t know.) You’re just using the word VALUE as if it does what
> you want.
Yes, it is valid SQL ... at least as understood by SQLite.
SQLite version 3.20.0 2017-07-07 22:47:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create view constants (col1, col2) as values (1,1), (1,2);
sqlite> .header on
sqlite> select * from constants;
col1|col2
1|1
1|2
sqlite>
> I was thinking of a CREATE TABLE command, and a SELECT command with "AS"
> clauses. Or of something similar with a VIEW.
The syntax create table ... as select ...
appears to be buggy?
sqlite> create table x (col1, col2) as select * from (values (1,2), (2,3));
Error: near "as": syntax error
however, omitting the column names appears to work, but you get the same column
names as the values clause directly.
create table x as select * from (values (1,2), (2,3));
sqlite> create table x as select * from (values (1,2), (2,3));
sqlite> select * from x;
|:1
1|2
2|3
in this case however
sqlite> create table x as values (1,2), (2,3);
sqlite> select * from x;
|:1
1|2
2|3
works just as well.
It would appear that you would need two statements to build a table with column
names from a values clause:
create table x (col1, col2);
insert into x values (1,2), (2,3);
I would suspect that using a table would be far more efficient then dynamically
re-generating the values every time they are needed anyway.
Of course, just using the values clause directly (without the select) seems to
generate different column names that are not carried forth ...
sqlite> values (1,2),(2,3);
column1|column2
1|2
2|3
sqlite> values ("1",2), (3,4);
column1|column2
1|2
3|4
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
