Re: [sqlite] UNION?

[Jim Dodgen]

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