Faulty insert results in TUniQuery

[rc]

I am working with Unidac components on an Oracle 10.2 database. I am executing the following SQL query from à TUniQuery component :

Code: Select all

INSERT INTO ROL_DEBITEUR (ID_PERSONNE, DEBITEUR_NOM, DEBITEUR_COMPLEMENT, NOM, PRENOM)
(SELECT DISTINCT ID_PERSONNE, DEBITEUR_NOM, DEBITEUR_COMPLEMENT, NOM, PRENOM
FROM ROL_TEMP
WHERE A_TRANSMETTRE = 1
AND ID_PERSONNE NOT IN (SELECT ID_PERSONNE FROM ROL_DEBITEUR));

But it seems to ignore the DISTINCT statement and tries to insert in the target tables the duplicate records found in source table, so breaking an unicity constraint.
Note that :
1/ when I execute this query directly in SQL Developer, all works fine,
2/ if I encapsulate the query in a stored procedure, it also works fine in SQL Developer, fot faulty in a TUniStoredProc component,
3/ The SELECT DISTINCT statement executed alone (without INSERT) returns correct results even in TUniQuery.

Has anyone got any explanation for that strange behaviour ?
Thank you very much by advance.

Roland

[AlexP]

Hello,


I couldn't reproduce the problem.

Please tell me what you mean by 'duplicate record'.
The DISTINCT Clause allows you to remove duplicates from the result set,
but if there are differences in at least one field, then it's already not a duplicate record.

Please send me a script to create and fill 'ROL_TEMP' and 'ROL_DEBITEUR' tables,
and I try to reproduce the problem again.

[tobias_cd]

@rc: You INSERT statement does look fine to me, too, as long as for example "ID_PERSONNE" is the ONLY unique key on "ROL_DEBITEUR".

If you still get unique constraint violations, than you have another column declared as uniqe key *without* the primary key column (id_personne?) in the constraint declaration.

Possible solutions:
* either change the secondary uniqe key to include the primary key column in it
-OR-
* add another "not in(select ...)" to exclude duplicates for (any other) secondary unique column(s) as well.

On a sidenote: I'd highly recommend to use "where not exists(select 1 from rol_debiteur r where r.id_personne = rol_debiteur.id_personne)" instead of "not in (select...)" since that is much faster and less work for the server, especially with many rows.