Hi Lester, long time, no conference!

select TICKET_ID, TRANSACT_NO, count(*)
from TRANSACTIONS
group by 1, 2
having count(*) > 1

HTH,
Set

Lester Caine wrote:

> As usual when I am in a rush something is not working as expected.
>
> I've got a simple metadata update which is failing because - INDEX is in use
>
> ALTER TABLE TRANSACTIONS
> DROP CONSTRAINT "PK_TRANSACTIONS",
> ADD CONSTRAINT "PK_TRANSACTIONS" PRIMARY KEY ("TICKET_ID", "TRANSACT_NO")
>
> Currently "TRANSACT_NO" is "TRANSACT" ( a timestamp is being changed to
> an integer )
>
> I've already applied this update to other databases, but for some reason
> the script that is creating TRANSACT_NO has managed to create duplicate
> records - so the failure message is a bit misleading :)
>
> CREATE UNIQUE INDEX "UI_TRANSACTIONS" ON TRANSACTIONS ("TICKET_ID",
> "TRANSACT_NO")
> gives duplicate values message so now I need to find them, my mind has
> gone blank on how to get COUNT("TICKET_ID", "TRANSACT_NO") > 1 can
> someone give me a kick please.
>
> Of cause what I will in next is add the UNIQUE index before running the
> update SP ;)