Hello list -

Please - can anyone help with the following issue.

(Environment = W2K ; FB 1 ; IBo 4.2 Gc )

I am trying to run the following update SQL

update JOBS set JOB_IS_ACTIVE = 'N' where JOB_NUM in
(
select JOB_NUM
from SP13
where HAS_WIP='N' and MTHS_AGO_LAST_TRAN is null and MTHS_AGO_OPENED

> 48)

SP13 is a select stored procedure that runs quite successfully as
follows (heavily edited down)

set term ^ ;
create PROCEDURE sp13
RETURNS ( JOB_ID INTEGER
, JOB_CO CHAR( 1 )
, JOB_NUM VARCHAR( 6 )
, mths_ago_opened integer
, mths_ago_commd integer
, JOB_COMMISSIONED_STATUS CHAR( 3 )
, mths_ago_first_tran INTEGER
, mths_ago_last_tran INTEGER
, has_wip char(1) )
AS
declare variable minsale date;
declare variable maxsale date;
declare variable MAXTRAN DATE;
declare variable MINTRAN DATE;
declare variable DATE_OPENED DATE;
declare variable DATE_COMMISSIONED DATE;
declare variable WIP_PY_START decimal (18,2);
declare variable WIP_CY_START decimal (18,2);
declare variable wip_now decimal (18,2);

BEGIN
for
select job_num, job_id, job_co, date_opened, DATE_COMMISSIONED ,
JOB_COMMISSIONED_STATUS, WIP_PY_START , WIP_CY_START , wip_now
from jobs
into job_num, job_id, job_co, date_opened, DATE_COMMISSIONED,
JOB_COMMISSIONED_STATUS, WIP_PY_START , WIP_CY_START , wip_now
do
begin
maxtran = '1-jan-1990'; mintran = current_date; mths_ago_commd =
null; has_wip = 'Y';

select min(trandate), max(trandate) from sales where job_num
= :job_num into minsale, maxsale;
<lots and lots deleted here..........................>
SUSPEND;
end
END ^
commit work ^

This update fails with the message

"ISC ERROR MESSAGE:
lock conflict on no wait transaction
deadlock"

As I am running this "stand alone" and I am only running only one
copy at a time I conclude that the problem is within my code rather
than with "interference" by other users.

I thought this might be caused by BEFORE UPDATE triggers on JOBS so
I've disabled them and still get the problem.

I thought that changing the transaction from NOWAIT to WAIT might
help - but no success with this either. So I am led to the conclusion
that the error is within the above code only.

I know I'm going to have to rewrite chunks of this so as to get FB
to "play ball" but before I waste yet more time beating my head
against a brick wall can some kind soul explain WHY I am getting the
error .

Many Thanks

John.