One way I get around this sort of problem is to have the view order itself by specifying an ORDER BY in the CREATE VIEW. This is limited, obviously, since the only times I want data out of this view, I want it in order, but if this is the case with your work, it should do nicely.
-George [EMAIL PROTECTED] On Tue, 2 Dec 2003, Fitzharris, Jason wrote: > 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. > > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
