I agree, Jim, it "would have been nice...". When I first posted about the problem I was having, I had gone through so SQL many experiments trying to get the query to work that had I lost track of which variations I had tried on which DBMS's. I inadvertently posted one of my versions of the query as a representation of the problem that I had tried with MS SQL Server but not yet tried with SQLite.
Lee Crain _____________________________________ -----Original Message----- From: Jim Dodgen [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 7:59 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? would have been nice to see the double parentheses in your example ... I have seen MS Access as well being overly parenthesized, what does the SQL92 standard say about that? Jim Lee Crain wrote: > Richard, > > Thanks for suggesting it but no, I don't think invisible control > characters are the problem. I have copied and pasted the query from my > source code into a MS SQL Server Management Studio interface and executed > it against a SQL Server mockup of our SQLite database. It works perfectly. > > I experimented and tried some variations on the query. > > ___________________________________________________________________ > > This query does not work in SQLite but works in MS SQL Server: > > sqlite> SELECT items_idx, [name], active FROM Items > ...> WHERE active = 'T' AND Items.items_idx IN > ...> ((SELECT related_item FROM RelatedItems WHERE item = 1777) > ...> UNION > ...> (SELECT item FROM RelatedItems WHERE related_item = 1777)) > ...> ORDER BY Items.name ASC; > SQL error: near "UNION": syntax error > sqlite> > > ____________________________________________________________________ > > However, this query works in both SQLite and MS SQL Server: > > sqlite> SELECT items_idx, [name], active FROM Items > ...> WHERE active = 'T' AND Items.items_idx IN > ...> (SELECT related_item FROM RelatedItems WHERE item = 1777 > ...> UNION > ...> SELECT item FROM RelatedItems WHERE related_item = 1777) > ...> ORDER BY Items.name ASC; > 1706|Arizona Character|T > 1707|Arizona Clothing and Props|T > 1660|Arizona Hair|T > 2325|Bonnie V3 Teen|T > 1425|Isabella for Stephanie 3|T > 1918|Little Darling for V3/SP|T > 106|Rose Character|T > 1778|Teresa Hair|T > sqlite> > > ____________________________________________________________________ > > Further experimentation showed that the extra pair of parentheses in the > first query (around each SELECT statement) caused the syntax error. > > Thanks for your response, > > Lee Crain > > ____________________________________________________________________ > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 08, 2007 3:38 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] UNION? > > "Lee Crain" <[EMAIL PROTECTED]> wrote: > >> The query at the bottom of this email is failing on the word "UNION". >> > (The > >> query works correctly in MS SQL Server.) >> > > Works when I try it.... Do you think you might have some > invisible control characters or something in the middle of > the SQL? > > >> __________________________________________________________________ >> SELECT Items.items_idx, Items.name, Items.active FROM Items >> WHERE active = 'T' AND Items.items_idx IN >> (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 >> UNION >> SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) >> ORDER BY Items.name ASC; >> >> > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > -------------------------------------------------------------------------- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -------------------------------------------------------------------------- > --- > > > > -------------------------------------------------------------------------- --- > To unsubscribe, send email to [EMAIL PROTECTED] > -------------------------------------------------------------------------- --- > > > > -------------------------------------------------------------------------- --- To unsubscribe, send email to [EMAIL PROTECTED] -------------------------------------------------------------------------- --- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
