Maybe I used a too simplistic example. I have code that generates SQL
queries so users can request reports with various fields, various
criterias and various sorting orders. So technicaly there can be 1,2,3,N
inner queries with various statements in them. Here is an example of a
more complex query:
SELECT t0.OBJECTS_BRIEF, t2.OBJECTS_ALPHANUMERIC_VALUE,
t3.OBJECTS_DATE_VALUE
FROM
(
(
(
SELECT *
FROM OBJECTS
WHERE OBJECTS_COLTYPE='Jewelry'
) AS t0
LEFT JOIN
(
SELECT *
FROM OBJECTS_ALPHANUMERIC
WHERE OBJECTS_ALPHANUMERIC_FIELD_ID='GRADE'
AND (OBJECTS_ALPHANUMERIC_VALUE='Excellent'
OR OBJECTS_ALPHANUMERIC_VALUE IS NULL)
) AS t1
ON t0.OBJECTS_ID = t1.OBJECTS_ID
)
LEFT JOIN
(
SELECT *
FROM OBJECTS_ALPHANUMERIC
WHERE OBJECTS_ALPHANUMERIC_FIELD_ID='OWNER'
AND NOT OBJECTS_ALPHANUMERIC_VALUE='John Smith'
) AS t2
ON t0.OBJECTS_ID = t2.OBJECTS_ID
)
LEFT JOIN
(
SELECT *
FROM OBJECTS_DATE
WHERE OBJECTS_DATE_FIELD_ID='EXHIBIT_END_DATE'
) AS t3
ON t0.OBJECTS_ID = t3.OBJECTS_ID
ORDER BY 3,2;
In the previous example, the user would have asked for
1- the item's description, it's owner it's exhibit end date
2- Objects that are Jewelry
3- Objects in excellent condition (grade) or unspecified
4- Objects not owned by John Smith
5- Sorted by exhibit end date and owner name
The "relationship" between OBJECTS and OBJECTS_ALPHANUMERIC is 0,N and
the "relationship" between OBJECTS and OBJECTS_DATE is also 0,N.
The previous query worked just fine in Oracle and Access, but not in
SQLite. SQLite might simply not be support it, that is what I want to
know. If it's a bug, a feature SQLite will support in the future (how
close in the future) or a feature that will never be supported.
Thank you all for your time
Simon Berthiaume
On Mon, 2004-01-19 at 14:24, Kurt Welgehausen wrote:
> If you look at the SQLite grammar in lang.html, you'll see
> that parentheses are not allowed around a table-list. That's
> why you're getting an error.
>
> If you remove either of the first 2 left parens (and its
> corresponding right paren), the query will work, but the
> outer select and the first subselect serve no purpose. It's
> hard to predict performance just by reading a query, but this
> looks pretty inefficient. How about
>
> select * from
> INSCLAIMS t0
> left join
> (select * from INSCLAIMS_CONCAT
> where INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO') t1
> on t1.INSCLAIMS_ID = t0.INSCLAIMS_ID;
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
