Union - Error - 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 - Error

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-02-26 : 09:31:51

Error: The text data type cannot be selected as DISTINCT because it is not comparable.

SELECT a.SAmAccountName, a.DOMAIN, a.EmployeeID, a.CustAtr, a.DisplayName, a.UPN, a.Date, a.flag, a.Date_Mod, a.Date_Exp,
a.IsActive, a.OU, b.EmployeeID AS EMPID_PEOPLEVIEW, b.LoginID, b.EMail, b.LastName, b.FirstName, b.MI, b.HireDate, b.TermDate, b.Rehiredate,b.LastModDate, b.Note
FROM dbo.CORP_EMP_IDS AS a INNER JOIN
dbo.PeopleView AS b ON
dbo.zeroExtend(a.EmployeeID) = dbo.zeroExtend(b.EmployeeID)
WHERE (a.IsActive = 1) AND (b.Active_Term = 'T') AND
(a.CustAtr <> '999999') AND
(a.SAmAccountName NOT IN ('yyyDR1', 'yyyDR2', 'yyyDR3', 'yyyEM1',
'yyyEM2', 'yyyEM3', 'yyyMG1', 'yyyMG2', 'yyyMG3', 'yyyHR1', 'yyyMM2', 'yyyMM3', 'yyyMM1', 'yyyHR2', 'yyyHR3', 'yyy2M1', 'yyy2M3', 'yyy2M2',
'yyyVP1', 'yyyVP2', 'yyyVP3'))

union

SELECT a.SAmAccountName, a.DOMAIN, a.EmployeeID, a.CustAtr, a.DisplayName, a.UPN, a.Date, a.flag, a.Date_Mod, a.Date_Exp, a.IsActive, a.OU, b.EmployeeID AS EMPID_PEOPLEVIEW, b.LoginID, b.EMail, b.LastName, b.FirstName, b.MI, b.HireDate, b.TermDate, b.Rehiredate, b.LastModDate, b.Note
FROM dbo.CORP_EMP_IDS AS a INNER JOIN
dbo.PeopleView AS b ON
dbo.zeroExtend(a.CustAtr) = dbo.zeroExtend(b.EmployeeID)
WHERE (a.IsActive = 1) AND (b.Active_Term = 'T') AND
(a.CustAtr <> '999999') AND (a.SAmAccountName NOT IN ('yyyDR1', 'yyyDR2', 'yyyDR3', 'yyyEM1',
'yyyEM2', 'yyyEM3', 'yyyMG1', 'yyyMG2', 'yyyMG3', 'yyyHR1', 'yyyMM2', 'yyyMM3', 'yyyMM1', 'yyyHR2', 'yyyHR3', 'yyy2M1', 'yyy2M3', 'yyy2M2', 'yyyVP1', 'yyyVP2', 'yyyVP3'))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 09:34:57

use UNION ALL instead of UNION

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-26 : 09:35:14

Either change query to union all or remove text column from the SELECT.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-02-26 : 11:34:34

Union All - worked perfect. thanks,

Can you please, please look into my other request for
UNION - Errror "Unable to parse query text." ?

Thanks Again,

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-02-29 : 11:26:28

Sorry! the union all did not work. I get duplicate records.
is there any other option?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-29 : 11:33:59

Of course you get duplicates, you are telling it to run what looks like the exact same query twice ... what are you trying to do? I am sure there is an easier way.

Also, is there any common attribute you can use (or create) to avoid listing out all of those SAmAccountName values? i.e., if all those values indicate an employee is "type X", then add a "type" column to your employees table and filter on that value, or create an attribute like that an AccountName table and join to that, or something along those lines.

more on that here:

http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-29 : 11:46:26

I'm not sure if this will work inyour case, but sometimes you can do a conditional join:

SELECT
	a.SAmAccountName,
	a.DOMAIN,
	a.EmployeeID,
	a.CustAtr,
	a.DisplayName,
	a.UPN,
	a.Date,
	a.flag,
	a.Date_Mod,
	a.Date_Exp,
	a.IsActive,
	a.OU,
	b.EmployeeID AS EMPID_PEOPLEVIEW,
	b.LoginID,
	b.EMail,
	b.LastName,
	b.FirstName,
	b.MI,
	b.HireDate,
	b.TermDate,
	b.Rehiredate,
	b.LastModDate,
	b.Note
FROM
	dbo.CORP_EMP_IDS AS a
INNER JOIN 
	dbo.PeopleView AS b
	ON dbo.zeroExtend(a.EmployeeID) = dbo.zeroExtend(b.EmployeeID)
	OR dbo.zeroExtend(a.CustAtr) = dbo.zeroExtend(b.EmployeeID)
WHERE
	a.IsActive = 1
	AND b.Active_Term = 'T'
	AND a.CustAtr <> '999999'
	AND a.SAmAccountName NOT IN('yyyDR1', 'yyyDR2', 'yyyDR3', 'yyyEM1', 'yyyEM2', 'yyyEM3', 
		'yyyMG1', 'yyyMG2', 'yyyMG3', 'yyyHR1', 'yyyMM2', 'yyyMM3', 'yyyMM1', 'yyyHR2', 
		'yyyHR3', 'yyy2M1', 'yyy2M3', 'yyy2M2', 'yyyVP1', 'yyyVP2', 'yyyVP3')

EDIT: Fixed line breaks for Jeff. ;)

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-02-29 : 11:51:59

The fields and the tables are same in both queries. But the condition is different
first one has dbo.zeroExtend(a.EmployeeID) = dbo.zeroExtend(b.EmployeeID) and the
second one has dbo.zeroExtend(a.CustAtr) = dbo.zeroExtend(b.EmployeeID)

These two fields EmployeeID and CustAtr supposed to have the same data.
some department query the database for employeeid and some custatr
I am trying to get the records that are terminated from both but i want all.
If first part returns 49 and the second returns 51

I want the first list and everything that are not common from the second list.
How would i do this? I tried union have distinct and data type error.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-29 : 12:00:38

Lamprey -- You should just about never join on an OR condition, you should use two separate OUTER JOIN's, one per condition. Then, just grab value(s) from either of the joined tables depending on whatever condition you need to check.

see:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-29 : 12:01:48

Also, Lamprey -- do me a favor an put a line break in your code, it stretches the screen out!!

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-29 : 12:04:02

Maachie -- the key when working out the logic on any SELECT is to remove the extra "stuff" and just focus on the important things. i.e., LastName, FirstName, etc are not important while you are working on the logic, so focus on just returning the key columns that you need and then add in those once you have things working. if you can simplify your situation and give a simple example with some sample data and expected results, we can help.

- Jeff
http://weblogs.sqlteam.com/JeffS

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-03-03 : 19:55:21

cast(Note as varchar(max)) -- 2005

cast(Note as varchar(8060)) -- 2000 and below: You could truncate your data this way but your gross results would be returned.

=======================================
Society is like a stew. If you don't keep it stirred up you get a lot of scum on the top. -Edward Abbey, naturalist and author (1927-1989)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources