| Author |
Topic |
|
qwertyjjj
Posting Yak Master
131 Posts |
 Posted - 2007-11-30 : 07:41:24
|
| AM trying to find duplicate notes in this table but get an error:select invno, note, count(*) from invoicenotes group by invno, note having count(*) > 1Server: Msg 306, Level 16, State 2, Line 1The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.Server: Msg 306, Level 16, State 1, Line 1The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.Is this possible with a text field? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-30 : 07:53:20
|
| what are the datatypes of invno and note?MadhivananFailing to plan is Planning to fail |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-11-30 : 08:00:18
|
the "note" column is text or ntext most likely.
elsasoft.org |
|
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2007-11-30 : 08:06:26
|
| invno is varcharnote is textSo, is this not possible with a text field?Is there a workaround? |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-11-30 : 09:13:05
|
this might work, perf may suck though. are your notes really longer than 8000 chars? if not you could use a varchar column instead.select invno, convert(varchar(8000),note), count(*) from invoicenotes group by invno, convert(varchar(8000),note) having count(*) > 1
elsasoft.org |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-11-30 : 09:14:26
|
also, if invno contains a number, consider using int instead of varchar for that column.
elsasoft.org |
|
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2007-11-30 : 09:52:40
|
| yep that worked. thanks.Actually the invoice number has leading zeros so I could strip these off and make it a number column.The performance difference won't be that much though will it? |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-11-30 : 10:44:41
|
it depends. if you are sorting on invno, or joining on it, the perf difference can be substantial. how many rows in the table?
elsasoft.org |
|
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2007-11-30 : 11:09:08
|
| 7,000 records.Grows by about 1,000 every month or so |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2007-11-30 : 13:02:16
|
| one bit of advice if you strip off leading zeros and convert to a numeric type:'00010' and '010' will both turn into 10 so be carefull creating dupes.Be One with the OptimizerTG |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-11-30 : 18:00:20
|
7000 rows is pretty tiny. I wouldn't bother. but that's me. I'm lazy.
elsasoft.org |
|
|
|
|
trying to find duplicates