Apparently you can't use ORDER BY on the first SELECT, so I presume that
the second ORDER BY will be applied to the combined rows.
Thanks
Mark
-----Original Message-----
From: Ben Conner [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 23, 2008 9:04 AM
To: CF-Talk
Subject: syntax issue between Access and MS SQL
Hi,
I have a CF application whose database I ported from MS Access to SQL
Server. After doing so I got the following error on a search:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
keyword 'UNION'.
SQL = "SELECT Products.*, Categories.*, Manufacturers.* FROM Products,
Categories, Manufacturers WHERE Products.ManufacturerID IN (Select
ManufacturerID from Manufacturers
WHERE Manufacturers.ManufacturerName Like '%spank%') AND
Categories.CategoryID = Products.CategoryID AND
Manufacturers.ManufacturerID = Products.ManufacturerID ORDER
BY Products.ProductName UNION ALL SELECT Products.*, Categories.*,
Manufacturers.* FROM Products, Categories, Manufacturers WHERE
Products.ProductID IN (Select ProductID from Products
WHERE Products.ProductName Like '%spank%' ) AND
Categories.CategoryID = Products.CategoryID AND
Manufacturers.ManufacturerID = Products.ManufacturerID
ORDER BY Products.ProductName"
This query runs fine when the target database is Access. Anyone have a
suggestion how to rephrase it or a syntax that works?
Thanks!
--Ben
+-------------------------------------------+
+ Ben Conner [EMAIL PROTECTED] +
+ Web World, Inc. 888-206-6486 +
+ PO Box 1122 480-704-2000 +
+ Queen Creek, AZ 85242 +
+-------------------------------------------+
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297111
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
