In postgres I can say:
test=# select * from (values(1),(3),(7)) as foo(id);
id
----
1
3
7
(3 rows)
This means that such a subselect can subsequently be joined with other tables using foo.id etc.
In sqlite I can say:
sqlite> select * from (values(1),(3),(7)) as foo;
----------
1
3
7
but if I say foo(id) I'll get an error:
sqlite> select * from (values(1),(3),(7)) as foo (id);
Error: near "(": syntax error
Obviously with usual subselects (e.g. "(select ... as a, ... as b from... ) as foo" ) you could simply name each field.
The only simple workaround I found was to do an union like:
sqlite> select 1 as id where 1=0 union values(1),(3),(7);
id
----------
1
3
7
Is there a better way to name columns in this kind of "subselects" in SQLite?
Aucun commentaire:
Enregistrer un commentaire