For those of you that tends to write complex queries, I noted that
SQLite doesn't like when a table name follows a opening parenthesis in
the FROM clause. For example, the following works under Access ans
Oracle, but not in SQLite:
SELECT T0.OBJECTS_ID , T0.OBJECTS_REFNO, T8.OBJECTS_LOCATION_LOCATION
FROM
(
OBJECTS AS T0
LEFT JOIN
(
SELECT OBJECTS_LOCATION_FOREIGN, MAX(OBJECTS_LOCATION_DATE) A
LATEST_DATE
FROM OBJECTS_LOCATION
GROUP BY OBJECTS_LOCATION_FOREIGN
) AS T9
ON T9.OBJECTS_LOCATION_FOREIGN = T0.OBJECTS_ID
)
LEFT JOIN
OBJECTS_LOCATION AS T8
ON (T8.OBJECTS_LOCATION_FOREIGN = T9.OBJECTS_LOCATION_FOREIGN AN
T8.OBJECTS_LOCATION_DATE = T9.LATEST_DATE)
WHERE T0.OBJECTS_FLAG = 3 AND T0.OBJECTS_DELETED = 0
ORDER BY 2 ASC;
To make it work, I have to write the following instead:
SELECT T0.OBJECTS_ID , T0.OBJECTS_REFNO, T8.OBJECTS_LOCATION_LOCATION
FROM
OBJECTS AS T0
LEFT JOIN
(
SELECT OBJECTS_LOCATION_FOREIGN, MAX(OBJECTS_LOCATION_DATE) AS
LATEST_DATE
FROM OBJECTS_LOCATION
GROUP BY OBJECTS_LOCATION_FOREIGN
) AS T9
ON T9.OBJECTS_LOCATION_FOREIGN = T0.OBJECTS_ID
LEFT JOIN
OBJECTS_LOCATION AS T8
ON (T8.OBJECTS_LOCATION_FOREIGN = T9.OBJECTS_LOCATION_FOREIGN AN
T8.OBJECTS_LOCATION_DATE = T9.LATEST_DATE)
WHERE T0.OBJECTS_FLAG = 0 AND T0.OBJECTS_DELETED = 0
ORDER BY 2 ASC;
I hope I will make someone save the trouble I went through finding that
out.
Simon Berthiaume
On Mon, 2004-01-19 at 16:37, Simon Berthiaume wrote:
> 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]
> >
