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] -----------------------------------------------------------------------------
