union of queries pls help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

union of queries pls help

Author

Topic

shobinmathew
Starting Member

16 Posts

Posted - 2008-05-29 : 09:52:48

Three sql statements whose result has to be unioned

SELECT
orders_recipients as Email
FROM vendors
where
vendorID in
(select Vendorid from Restaurants_VendorsAssoc where restaurantid = @resid)
union
SELECT str_eMail as EMail from tbl_contactmanager where (bit_shared =1) and int_restaurantId in (select restaurantid from users where userID in (select UserID2 from Users_Linked where UserID1 = @userid))
union
SELECT str_eMail as EMail from tbl_contactmanager where int_restaurantId = @resid

I am getting an error message as follows
The text, ntext, or image data type cannot be selected as DISTINCT.
pls help

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-29 : 10:05:51

try change your UNIONs to UNION ALLs

Be One with the Optimizer
TG

shobinmathew
Starting Member

16 Posts

Posted - 2008-05-30 : 01:12:53

i got it done successfully with
select
convert(varchar(500),str_eMail)
as
EMail

The error was sue to the fact that the data types of the email field should be same in all the select statments in a Union

chetanb3
Yak Posting Veteran

52 Posts

Posted - 2008-05-30 : 02:08:26

column having "text" datatype can not be used with DISTINCT clause.
so you have to convert it to varchar.

Thanks,
Chetan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-30 : 07:17:19

glad you got it working but just to clerify a couple things:

- you're right that the datatypes need to be consistent between the UNIONed parts but that was not the cause of your error. If you had CONVERTED the other value to TEXT then even though the dataytypes were consistent you would still get that error.

- chetanb3, your statement is correct but the OP did not use the DISTINCT clause in his statement. The UNION (rather than UNION ALL) implied distinctness between the statements.

- Unless you want to explicitly DISTINCT the values you should use UNION ALL because it saves sql server from distincting the values between the multiple parts. ie: it is more efficient.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources