Adam wrote:

> Hello Group,
>
> I have the following scenario that although I can solve it two ways so
> far, I am not happy with the plan it generates.
>
> TableA
>
> ID FieldA FieldB
> -- ------ ------
> 1 1 2
> 2 1 2
> 3 3 4
> 4 1 2
> 5 3 5
> 6 3 5
> 7 1 7
>
> Basically, I need to get all records with duplicated FieldA, FieldB
> combinations.
>
> The query will end up in a PSQL for select block.
>
> I will then remove these records, and insert a new record for this
> combination. (for example, it would remove records 1,2 and 4 and
> insert record ID 8 with 1,2).
>
> (This is a notification table)
>
> I can do the following:
>
> select FieldA, FieldB
> from TableA
> group by 1,2
> having count(*)>1
>
> And get the right records, but I don't really need to visit and count
> each of the records (I just need to know if at least one other
> matching record exists).
>
> I have tried several variations of joining back to itself and using
> exists clauses but they still need to do a natural read of TableA, and
> if it is going to do that, then the count(*) is just as efficient.
>
> Any ideas are appreciated.
>
> Adam
>
>
>

Hi,

If you need to remove duplicate rows within unnamed cursor you can try
using a new UNIQUE field.

For example having a FIELD12 defined as unique, you can do smth like

FOR SELECT ID FROM yourtable INTO :varID
AS CURSOR C
DO
BEGIN
UPDATE FIELD12=field1||field2;
WHEN SQLCODE -803 DO DELETE FROM yourtable WHERE CURRENT OF C;
END

It will only remove duplicate values, it does not create a new record
from duplicate but you can do UPDATE ID=<some_generator_value>.
It will still parse the whole table but you might embed it into your
PSQL code.
It is a twisted-mind solution,true, but it might help.

Radu