A colleague is running the following in ISQL (for ASA9.0.2.3527)

----------
create variable current_transaction varchar(30);
set current_transaction='PASU256';

DELETE auction.note
FROM auction.note_type
WHERE note_type.note_type_id = note.note_type_id
AND note_type.code = 'Extra'
AND note.text = 'This Vehicle is Located In Alice Springs';

rollback;

drop variable current_transaction;
----------

(The ROLLBACK is only there until we resolve this issue)

During execution the warning comes up:

"Null value eliminated in aggregate function" (SQLCODE=109, ODBC 3
State="01003") Line 13, column 1 (on the DELETE statement).

Examining the NOTE table, it has a trigger before delete which reads
as follows:

----------
ALTER TRIGGER "tdb_note" .tdb_note before delete on auction.note
referencing old as old_del
for each row
begin
if current remote user is null then
insert into auction.note_audit(note_id,
transaction_date,transaction_type,transaction_user ,
transaction_name) values(
old_del.note_id,getdate(*),'D',current user,current_transaction)
end if
end
----------

There are no other triggers on the table.

Selecting the rows that are supposed to be deleted shows 432 records.
Using the Profiling feature shows that the INSERT statement in this
trigger is called 432 times.

Any suggestions why we're getting this aggregate function warning?
Similar triggers on other tables haven't caused this issue (that we're
aware of!)

The NOTE table schema is:

----------
CREATE TABLE "auction"."note" (
"note_id" integer NOT NULL,
"note_type_id" integer NOT NULL,
"note_ref_id" integer NOT NULL,
"internal_only_flag" char(1) NULL,
"text" varchar(250) NULL,
"date_created" timestamp NULL,
"date_last_modified" timestamp NULL,
"created_by" varchar(30) NULL,
"last_modified_by" varchar(30) NULL,
"added_by_pda_flag" char(1) NULL DEFAULT 'N',
"email_message_id" integer NULL,
PRIMARY KEY ( "note_id" )
);
----------

and the NOTE_AUDIT schema is:

----------
CREATE TABLE "auction"."note_audit" (
"note_id" integer NOT NULL,
"transaction_date" timestamp NOT NULL,
"transaction_type" char(1) NOT NULL,
"transaction_user" varchar(30) NOT NULL,
"transaction_name" varchar(30) NOT NULL,
"internal_only_flag" char(1) NULL,
"text" varchar(250) NULL,
"note_type_id" integer NOT NULL DEFAULT '',
"note_ref_id" integer NOT NULL DEFAULT ''
);
----------

TIA.

Chris.

Perhaps there is a foreign key referencing one of the tables where the
deletes are happening that has ON DELETE CASCADE, and the table with that
foreign key has a trigger. Executing:
select traceback(*)
after the delete might give a clue as to where the problem is happening.

Whitepapers, TechDocs, bug fixes at http://www.ianywhere.com/developer

"youcantryreachingme" <youcantryreachingme (AT) gmail (DOT) com> wrote

Hi Bruce,

thanks for the suggestions.

Traceback returned null.

The note table references the note_type table; it also references a
second table named email_message. It has no referencing tables.

The note_type table has no foreign keys and the only referencing table
is note.

The warning occurs on my colleagues local development database, my
local development database and our team development database, but not
on our UAT database; that is, I just ran (in ISQL, UAT database) a
statement "read X:\filename\etc" where the file named contained the
delete statement, and there was no warning dialogue box. I don't know
whether the same warning was still triggered but perhaps the dialogue
box was suppressed because of the "read" statement.

Chris.

I have no other suggestions at this point, other than to contact Tech
Support with a repro that can be examined further to find the cause.

Whitepapers, TechDocs, bug fixes at http://www.ianywhere.com/developer

"youcantryreachingme" <youcantryreachingme (AT) gmail (DOT) com> wrote