At 05:38 PM 23/02/2004 -0600, you wrote:

>Hi,
>
> I have been trying to use the statement you gave me, but I keep coming up
>with this error:
>
> -803 Attempt to store duplicate value (visible to active transactions) in
>unique index "<string>" 335544349L
>
> After some google'n, I have come to realize that some of the parts were in
>the database twice. Once with the two leading chars, and once without. So
>am I right to assume this is the cause of this error?

Yes, since you have a unique index on the column.

> Since we only need one instance of the part, I decide to delete the
>duplicates and try again. However, when I try this statement it returns with
>no data instantly, and gives no error message:
>
> for
>
> select PARTNUM from PARTSTABLE where PARTNUM starting with 'MS'
>into :MYSTR
>
> do begin
>
> delete from ITEM where ITEMCODE=SUBSTRING(:MYSTR from 3 for 28);
>
> end

When you say "I tried this statement", I presume that you mean you wrote a
stored procedure with that statement in it?

A DELETE statement doesn't return any data. It just "does its thing" -
deletes rows. You'll only get "feedback" if it finds a row locked - in
which case the whole operation will fail and nothing happens.

Did you query the database after committing your EXECUTE PROCEDURE
statement, to discover whether any of the duplicates were still there?

you could do

select
p1.partnum from mytable p1
from partnum p1
where p1.partnum CONTAINING 'MS'
union
select p2.partnum from mytable p2
where p2partnum STARTING WITH 'MS'

order by 1

> I ever tried many variations on that (including some where I changed
>keywords to non-keywords and other invalid statements), and I still got no
>feedback what so ever. I have all permissions on the database. This should
>work, no?

I think you first need to find out if it is, in fact, NOT working.

>This is all in IBConsole, is there some better way to access the
>data?

Some would say "almost anything is better than IBConsole". But, for these
queries, it should be OK, as long as you ***commit*** the work and then
refresh your "checkup" query afterwards.

/heLen