Dominique, Thanks for your response and for breaking down the select into manageable pieces!
According to the limits info, there should be no problem with the # of terms in that select statement (< 500). There are hundreds of these predefined queries that I am extracting out of the MS access databases, so it is unlikely that I'd be able to break them down into component queries, particularly since they are already referenced as named (predefined) queries in the software out in the field. I was hoping to move away from Access toward SQLite if feasible. I feel that it would be faster and easier to port to other platforms. I'm not an SQL expert either, but you're certainly better at it than I am. I get lost after a couple of joins. I wonder if there is a way to re-arrange the joins so that the select would be acceptable to SQLite. Vance on Mar 12, 2013, Dominique Devienne <ddevie...@gmail.com> wrote: > >I'm no expert, but I think your problem comes from the excessive >parentheses you are using. > >Below's a little experiment that reproduces your syntax, with its error, >and provides an alternative simpler syntax that works. --DD > >C:\Users\DDevienne>sqlite3 >SQLite version 3.7.15.2 2013-01-09 11:53:05 >Enter ".help" for instructions >Enter SQL statements terminated with a ";" >sqlite> create table a (id number); >sqlite> create table b (id number, a number); >sqlite> create table c (id number, b number); >sqlite> create view v1 as select a.id, b.id, c.id > ...> from (c INNER JOIN (a INNER JOIN b on a.id = b.a) on c.b = b.id) > ...> where (((a.id) = 1)); >Error: no such column: a.id >sqlite> >sqlite> create view v1 as select a.id, b.id, c.id > ...> from > ...> a INNER JOIN b on a.id = b.a > ...> INNER JOIN c on b.id = c.b > ...> where a.id = 1; >sqlite> > > >On Tue, Mar 12, 2013 at 3:42 PM, <ven...@intouchmi.com> wrote: > >> >> Hello, >> >> Using sqlite3, I entered this create view statement: >> CREATE VIEW IF NOT EXISTS "Annies Form" AS >> SELECT Categories.Category_Name, Strings.Long_Text, Units.Long_Units, >> Limits.Lower_Limit, Limits.Upper_Limit >> FROM ((((Objects INNER JOIN (Categories INNER JOIN Object_Categories ON >> Categories.Category_ID=Object_Categories.Category_ID) ON >> Objects.Object_ID=Object_Categories.Object_ID) INNER JOIN Units ON >> Objects.Unit_ID=Units.Unit_ID) INNER JOIN Strings ON >> Objects.String_ID=Strings.String_ID) INNER JOIN Addresses ON >> Objects.Address_ID=Addresses.Address_ID) INNER JOIN Limits ON >> Objects.Limit_ID=Limits.Limit_ID >> WHERE (((Categories.Category_Type_ID)=1)); >> >> sqlite3 complains that the column Categories.Category_Name does not exists. >> Yet, I can do a >> Select Categories.Category_Name From Categories >> with no problems. >> >> This create view worked as well: >> CREATE VIEW IF NOT EXISTS 'Select_Categories' AS >> SELECT Categories.Category_ID, Categories.Category_Name, >> Strings.Long_Text, Strings.Short_Text, Strings.Alternate_Text, >> Category_Types.Category_Type >> FROM (Categories INNER JOIN Strings ON Categories.String_ID = >> Strings.String_ID) INNER JOIN Category_Types ON Categories.Category_Type_ID >> = Category_Types.Category_Type_ID; >> >> As a side note, these were predefined queryDefs from an MS Access db that, >> I believe, work OK. >> >> Vance >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
