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

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

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

regards

Adam

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


Have Fun
Rhys

Me too. Thanks, Jeff

Giorgio