Thanks, I tried the square brackets with the table name qualifier and it worked fine.
By the way, great product. I'm so used to the bloatware of Microsoft, IBM and Oracle that I just can't figure out how you could develop a very functional database with one small dll. Regards, Jason. -----Original Message----- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: 04 December 2003 13:20 To: Fitzharris, Jason Cc: [EMAIL PROTECTED] Subject: Re: [sqlite] Can't order a view with joins 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. > 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]
