On Sun, 17 Mar 2019 14:27:19 -0600
"Keith Medcalf" <kmedc...@dessus.com> wrote:
>
> specifies the name you want to appear for the first column. The other names
> for the other four columns are free to be whatever they want ... that is,
> without an AS clause, the column names are not guaranteed to be anything
> other than a random basket of characters though SQLite will attempt to return
> the underlying table and column name (from the table declaration).
Good to know, thanks for that point. For SQL query text to be saved and stay
for long, I will always use AS.
> >Aside, what also surprised me just a moment ago and I never noticed
> >before, is that although I can refer to ROWID (not aliased) anywhere
> >in a query, it seems to not work properly in `using(rowid)`, if the
> >ROWID is automatically generated in one of the table (ex. an ordinary
> >table with no “without rowid”) and it is not in the other table (ex.
> >a column named ROWID in a VIEW).
>
> You will have to provide an example.
>
> t1 JOIN t2 USING (ROWID) works perfectly fine for me when ROWID is an
> explicitly defined rowid (integer primary key in a rowid table). Are you
> trying to perform a join using the ephemeral ROWID?
>
Here an example:
create table test (a int, b int);
insert into test (a, b) values (1, 2);
insert into test (a, b) values (3, 4);
insert into test (a, b) values (5, 6);
insert into test (a, b) values (7, 8);
create view testview (rowid, sum) as select rowid, a + b from test;
select rowid from test; -- OK, the column exists.
select rowid from testview; -- OK, the column exists.
select testview.sum from testview, test using (rowid) where test.a <= 3; --
Fails.
-- SQLite complains: “cannot join using column rowid - column not present
in both tables”.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
