Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 16th, 2003, 12:38 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 347
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Adam H-W
Default SQL QA Analyzer error

Hi there

I'm getting an error in my sql statement and am not sure why. I'm trying to append to select statements and output the result as one.

Here's the statement:

SELECT sp.ProductID, sp.SubCatID, sp.ProductCode, sp.ProductName, sp.ProductShort,
sp.ProductLong, sp.Price, b.Quantity FROM SpecialProducts sp, Basket b, Products p
WHERE sp.ProductID = p.ProductID AND b.CustomerID = '3' UNION SELECT
p.ProductID, p.CategoryID, p.SubCategoryID, p.ProductCode, p.ProductShort,
p.ProductLong, p.Price, b.Quantity FROM Products p, basket b
WHERE p.ProductID = b.ProductID AND b.CustomerID = '3'

and I get this error:

The text, ntext, or image data type cannot be selected as DISTINCT.

Any help greatly appreciated.

Many thanks

Adam
 
Old December 16th, 2003, 01:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

I'm surmising that one (or more) of the columns in your query is defined as a text (ntext) type.

The UNION operator by default eliminates duplicate rows from the resultset. The duplicate eliminating operation is similar to a SELECT DISTINCT. You can't use SELECT DISTINCT on a column that is text type.

Try UNION ALL which will not remove duplicates, or remove the text column(s).

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old December 16th, 2003, 02:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 347
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Adam H-W
Default

Great, thanks Jeff - I took the option of changing the text Data Type

regards

Adam
 
Old June 26th, 2005, 04:15 PM
Registered User
 
Join Date: Jun 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, I had same problem. Using UNION ALL instead of UNION made the difference.


Have Fun
Rhys


 
Old July 14th, 2005, 05:16 AM
Registered User
 
Join Date: Jul 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Me too. Thanks, Jeff

Giorgio






Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO.Net slower than SQL Server Query Analyzer s5g5r ADO.NET 1 January 26th, 2005 09:49 PM
SQL Query Analyzer bmains SQL Server 2000 3 June 23rd, 2004 05:58 PM
SQL Query Analyzer Save As ioates SQL Server 2000 1 May 18th, 2004 08:01 AM
xml result set truncated in sql query analyzer xologist SQL Server 2000 2 March 22nd, 2004 05:23 PM
Debugging in SQL 2000 QA chrislepingwell SQL Server 2000 3 June 25th, 2003 12:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.