Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and Relational Databases and the fly likes SQL Server Exception Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Server Exception" Watch "SQL Server Exception" New topic
Author

SQL Server Exception

Muhammad Imad Qureshi
Ranch Hand

Joined: Sep 13, 2005
Posts: 238
I am using follwoing query

SELECT Fax.* FROM Fax
INNER JOIN FaxAccess ON Fax.DocumentNumber = FaxAccess.DocumentNumber
WHERE FaxTypeId = 2 AND isPublic = 0 AND
(FaxAccess.UserAccess LIKE '\Seattle\Police\Detective%' )

UNION

SELECT Fax.* FROM FAX
WHERE FaxTypeId = 2 AND
Fax.isPublic = 1

and both queries return same columns independently but I get following error when I execute the queries with union

Server: Msg 8163, Level 16, State 4, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.

I would appreciate if someone can help me out. By my question you can guess I am a novice.
Muhammad Imad Qureshi
Ranch Hand

Joined: Sep 13, 2005
Posts: 238
I am found the solution. In one of my column I had an image file.
and because I have one image file in one of my columns that I am selecting
thats why I should do UNION ALL because 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 or image type or ntext type.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 32481
    
214

Muhammad,
Thanks for posting the solution!


[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
 
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com
 
subject: SQL Server Exception
 
It's not a secret anymore!