Hi, I am getting an error when trying to do an order by on a view which has a join. To replicate, create the following two tables and the view.
CREATE TABLE tabTest_1 ( Field_1_1 CHAR(10), Field_1_2 CHAR(10), Field_1_3 CHAR(10), Field_1_4 CHAR(10) )
CREATE TABLE tabTest_2 ( Field_2_1 CHAR(10), Field_2_2 CHAR(10), Field_2_3 CHAR(10), Field_2_4 CHAR(10) )
CREATE VIEW vTest AS SELECT * FROM tabTest_1, tabTest_2 WHERE Field_1_1 = Field_2_1
There is no need to insert any data. Now do a simple select from the view like so.
SELECT * FROM vTest
This works fine as it returns all eight columns. Now try and run this query.
SELECT * FROM vTest ORDER BY Field_1_1
This fails with the message "Select Error: no such column: Field_1_1" However if I drop the view and recreate it by specifying all of the fields like so.
CREATE VIEW vTest AS SELECT Field_1_1, Field_1_2, Field_1_3, Field_1_4, Field_2_1, Field_2_2, Field_2_3, Field_2_4 FROM tabTest_1, tabTest_2 WHERE Field_1_1 = Field_2_1
Now if I run the second query with the order by it works fine.
Is this a bug with SQLite? And if so, is there any indication if/when it will be fixed?
I have many views which join several tables which have many columns, and I don't want to spend ages writing out all of the columns if a fix is pending.
The problem is in the way SQLite names columns that result from a join. The names of the result columns always include the name of the table. So the first column of vTest is named [tabTest_1.Field_1_1]. And the second column is named [tabTest_1.Field_1_2]. And so forth. Other database engines, it seems, omit the table name from the resulting column names and call the results just Field_1_1 and Field_1_2 and so forth.
There are several open tickets on this problem already.
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
