Data type of a tuple id in r3?

In the past I've used a construct of the following to get rid of duplicates as a last resort.

delete from table1
where tid in
  (select t1.tid from table1.t1, table2 t2
     where t1.field1 = t2.field1
       and t1.field2 = t2.field2
       and t1.tid < t2.tid)

This doesn't seem to work in r3 on HP-UX 11i. Whether there is any cause and effect or just coincidence, the character set of this particular installation is SHIFT-JIS. The data in field1 and field2 are English characters.

After much consternation I found the following to work.

delete from table1
where int4(tid) in
  (select int4(t1.tid) from table1.t1, table2 t2
     where t1.field1 = t2.field2
       and t1.field2 = t2.field2
       and int4(t1.tid) < int4(t2.tid))

Have I just been lucky in the past that the first one worked?

How I finally figured out I needed to cast the tid was when I did a select of min(tid) as min_tid, field1, field2 as part of a create table statement, and did an examine in qbf, the data type column of min_tid had garbage in it. Plus, any dml against this table caused an error. Seeing this I was surprised the create table worked at all.

Cheers

Dennis

d underscore roesler at agilent dot com
.

Relevant Pages