As a follow up, what I meant to ask is why it doesn't work.
I rewrote the original select:
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));
To:
SELECT "Categories"."Category_Name", "Strings"."Long_Text",
"Units"."Long_Units", "Limits"."Lower_Limit", "Limits"."Upper_Limit"
FROM (((((Categories INNER JOIN Object_Categories ON
"Categories"."Category_ID"="Object_Categories"."Category_ID") INNER JOIN
Objects 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));
Which does seem to work. Is my rewrite the same as the original?
Vance
on Mar 12, 2013, ven...@intouchmi.com wrote:
>
>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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
