By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

How to use UNION ALL and SORT BY

P: n/a
Hi,

I have an asp site where I sell some products. Product categories are
stored in different tables. There are different asp product pages,
which collects data from these tables.
I have also created an all-product-page, which works fine with the
following sql query:

SELECT * FROM kaldoi
WHERE (id LIKE 'KALDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkdoi
WHERE (id LIKE 'MKDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkfut
WHERE (id LIKE 'MKFUT%' AND soldout <> 1)

This page lists all my products by product category.
I'd like to sort the results by "productid", however when I insert
'SORT BY productid' at the end of the statement, I receive the
following error message from my editor (FP2003):

Server error: Unable to retrieve schema information from the query:

SELECT * FROM kaldoi
WHERE (id LIKE 'KALDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkdoi
WHERE (id LIKE 'MKDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkfut
WHERE (id LIKE 'MKFUT%' AND soldout <> 1)
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(id LIKE 'MKFUT%' AND soldout <> 1)
SORT BY productid'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
I couldn't solve it, though I have browsed through dozens of threads
here.
Any help will be appreciated

Nov 28 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
It's:
ORDER BY productid

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<to******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hi,

I have an asp site where I sell some products. Product categories are
stored in different tables. There are different asp product pages,
which collects data from these tables.
I have also created an all-product-page, which works fine with the
following sql query:

SELECT * FROM kaldoi
WHERE (id LIKE 'KALDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkdoi
WHERE (id LIKE 'MKDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkfut
WHERE (id LIKE 'MKFUT%' AND soldout <> 1)

This page lists all my products by product category.
I'd like to sort the results by "productid", however when I insert
'SORT BY productid' at the end of the statement, I receive the
following error message from my editor (FP2003):

Server error: Unable to retrieve schema information from the query:

SELECT * FROM kaldoi
WHERE (id LIKE 'KALDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkdoi
WHERE (id LIKE 'MKDOI%' AND soldout <> 1)
UNION ALL
SELECT * FROM mkfut
WHERE (id LIKE 'MKFUT%' AND soldout <> 1)
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(id LIKE 'MKFUT%' AND soldout <> 1)
SORT BY productid'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
I couldn't solve it, though I have browsed through dozens of threads
here.
Any help will be appreciated

Nov 28 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.