Link to home
Create AccountLog in
Avatar of rgb192
rgb192Flag for United States of America

asked on

add to a query, dont want value 'test'


CREATE TABLE [dbo].[paypaldata](
      [ppid] [int] IDENTITY(1,1) NOT NULL,
      [ppdate] [datetime] NULL,
      [ppdata] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ppdata2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]




this works

select t.*
  from paypaldata t
 where cast(t.ppdata as varchar(max)) in ( select cast(i.ppdata as varchar(max)) from paypaldata i where i.ppdata is not null group by cast(i.ppdata as varchar(max)) having count(*) > 1 )


I dont want results where ppdata is 'test'


Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the not equal to operator.


select t.*
  from paypaldata t
 where cast(t.ppdata as varchar(max)) in ( select cast(i.ppdata as varchar(max)) from paypaldata i where i.ppdata is not null and i.ppdata!='test' group by cast(i.ppdata as varchar(max)) having count(*) > 1 )
Avatar of Nathan Riley
Nathan Riley
Flag of United States of America image

so why don't you just change it to not in?
select t.*
from paypaldata t
where cast(t.ppdata as varchar(max)) not in 
		( select cast(i.ppdata as varchar(max)) 
		from paypaldata i 
		where i.ppdata is not null and i.ppdata!='test' 
		group by cast(i.ppdata as varchar(max)) 
		having count(*) > 1 )

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Nathan Riley
Nathan Riley
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of rgb192

ASKER

thanks