Msg 306 Error

[Khanson82]

The below pulls everything I need except one field that I get errors when I try to add it.
I'm needing c.notes.

I get this error right now;
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Sql Server 2005


select f.clientname,a.caseno,a.applid, a.applname,b.providerid, (a.status + ' / ' + a.statusreason)as Status, b.appt_datetime,max(c.adddttm) as RejectTime,c.notes
from VWA0 a with(nolock)
inner join VWAC f on
f.clientid = a.clientid
left outer join HHA b with(nolock)
on a.serial = b.appserial
and b.coid = f.clientname
and b.serial in
(select max(serial) from HHA with(nolock)
where appt_status in ('Appointment Complete','Appointment Rescheduled','Appointment Pending',
'Appointment Cancel')
group by appserial)
left outer join VWR c with(nolock)
on a.serial = c.appserial
and a.clientid = c.clientid
and c.notes like '% %'
and c.requirement = ('qc')
where a.status in ('corrections','returned by client','corrections submitted')
group by f.clientname,a.caseno,a.applid, a.applname,
b.providerid, (a.status + ' / ' + a.statusreason), b.appt_datetime,c.notes

 

[markros]

What is your SQL Server version?

PluralSight Learning Library

 

[Khanson82]

2005 studio express

 

[SQLBill]

What datatype is appt_status and a.status? If they are text, ntext, or image, you can't use IN. You would have to do something like:

WHERE (appt_status LIKE '% Appointment Complete %'
OR appt_status LIKE '% Appointment Rescheduled %'
OR appt_status LIKE '% Appointment Pending %'
OR appt_status LIKE '% Appointment Cancel %')


or maybe

WHERE (appt_status LIKE '% Appointment %'

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

Have you thought about converting your ntext columns to varchar(max) and your text columns to varchar(max)? If you did this, your problem would go away.

If you decide to do this, I encourage you to test the conversion on a backup copy of your database first.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Khanson82]

appt_status is varchar
a.status is varchar
c.notes is text

I tried the like changeout and still recieved the same message?

I would love to convert the columns in the tables but i don't think anyone here would go for it since it would be quite a lengthy process to make that happen where i work...

 

[gmmastros]

I would love to convert the columns in the tables but i don't think anyone here would go for it since it would be quite a lengthy process to make that happen where i work...

I can understand that. You should put this on your medium range to do list though. Working with Text and nText is a super pain in the butt, and it's not necessary because SQL2005 (and up) has a better way of handling this type of data. Anyway, since you are using SQL2005, I would suggest you try this...

select f.clientname,a.caseno,a.applid, a.applname,b.providerid, (a.status + ' / ' + a.statusreason)as Status, b.appt_datetime,max(c.adddttm) as RejectTime,[!]Convert(VarChar(max),[/!] c.notes[!]) As notes[/!]
from VWA0 a with(nolock)
inner join VWAC f on
f.clientid = a.clientid
left outer join HHA b with(nolock)
on a.serial = b.appserial
and b.coid = f.clientname
and b.serial in
(select max(serial) from HHA with(nolock)
where appt_status in ('Appointment Complete','Appointment Rescheduled','Appointment Pending',
'Appointment Cancel')
group by appserial)
left outer join VWR c with(nolock)
on a.serial = c.appserial
and a.clientid = c.clientid
and c.notes like '% %'
and c.requirement = ('qc')
where a.status in ('corrections','returned by client','corrections submitted')
group by f.clientname,a.caseno,a.applid, a.applname,
b.providerid, (a.status + ' / ' + a.statusreason), b.appt_datetime,[!]Convert(VarChar(max),[/!] c.notes[!]) As notes[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh... sorry. You'll need to remove the alias in the group by clause.

select f.clientname,a.caseno,a.applid, a.applname,b.providerid, (a.status + ' / ' + a.statusreason)as Status, b.appt_datetime,max(c.adddttm) as RejectTime,[!]Convert(VarChar(max),[/!] c.notes[!]) As notes[/!]
from VWA0 a with(nolock)
inner join VWAC f on
f.clientid = a.clientid
left outer join HHA b with(nolock)
on a.serial = b.appserial
and b.coid = f.clientname
and b.serial in
(select max(serial) from HHA with(nolock)
where appt_status in ('Appointment Complete','Appointment Rescheduled','Appointment Pending',
'Appointment Cancel')
group by appserial)
left outer join VWR c with(nolock)
on a.serial = c.appserial
and a.clientid = c.clientid
and c.notes like '% %'
and c.requirement = ('qc')
where a.status in ('corrections','returned by client','corrections submitted')
group by f.clientname,a.caseno,a.applid, a.applname,
b.providerid, (a.status + ' / ' + a.statusreason), b.appt_datetime,[!]Convert(VarChar(max),[/!] c.notes[!])[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

;with SomeInfo as (select f.clientname,a.caseno,a.applid, a.applname,b.providerid, (a.status + ' / ' + a.statusreason)as Status, b.appt_datetime,max(c.adddttm) as RejectTime
from VWA0 a with(nolock)
inner join VWAC f on
f.clientid = a.clientid
left outer join HHA b with(nolock)
on a.serial = b.appserial
and b.coid = f.clientname
and b.serial in
(select max(serial) from HHA with(nolock)
where appt_status in ('Appointment Complete','Appointment Rescheduled','Appointment Pending',
'Appointment Cancel')
group by appserial)
left outer join VWR c with(nolock)
on a.serial = c.appserial
and a.clientid = c.clientid
and c.notes like '% %'
and c.requirement = ('qc')
where a.status in ('corrections','returned by client','corrections submitted')
group by f.clientname,a.caseno,a.applid, a.applname,
b.providerid, a.status, a.statusreason, b.appt_datetime)

select SomeInfo.*, VWR.Notes from SomeInfo inner join VWR
on SomeInfo.RejectTime = VWR.Adddtm

PluralSight Learning Library

 

[Khanson82]

The ;cte worked great.. Thanks again to you both for the help once again!