hi,

I have an UPDATE sql statement in SPs on SQL SERVER database, like this:

update EMP_TAXDET set THR = (THR +
(dbo.decode(b.x_amount)*isnull(cc.crate,1)))
from emp_taxdet a,payroll b,employee c,crate cc
where a.empid = b.empid
and b.batchno = @BATCHNO
and b.ptype = 'THR' and a.pdate = convert(datetime,@DTBATCH,103)
and c.empid = a.empid
and cc.ccode =* c.ccode
and cc.cyear = year(convert(datetime,@DTBATCH,103))
and cc.cmonth = month(convert(datetime,@DTBATCH,103))
and c.comid = @COMID and c.stat_empl <> 5

Now, I need to convert this to Firebird.
I have difficulties on how to convert the above UPDATE statement in
Firebird, since Firebird doesn't allow from (table name) inside UPDATE
statement.

I tried to solve this problem by creating like these:

SET TERM ^^ ;
CREATE PROCEDURE SP_SMALL_PROCESSTAX returns (THR Numeric(18,2))
AS
declare variable val_x_amount varchar(15);
declare variable val_crate numeric(18,2);
declare variable val_thr numeric(18,2);
BEGIN
for select a.x_amount,b.crate,c.thr
from payroll a, crate b, emp_taxdet c
into :val_x_amount, :val_crate, :val_thr
do
begin
THR = (:val_thr + ((:val_x_amount)*(:val_crate)));
end

END
^^
SET TERM ; ^^

update EMP_TAXDET set THR = (select thr from sp_small_processtax)
where emp_taxdet.empid in (select empid from payroll)
and :BATCHNO in (select batchno from payroll)
and 'THR' in (select ptype from payroll)
and emp_taxdet.pdate = :DTBATCH
and emp_taxdet.empid in (select empid from employee)
-->and crate.ccode = employee.ccode
and crate.cyear = mer_year(:DTBATCH)
and crate.cmonth = mer_month(:DTBATCH)
and employee.comid = :COMID
and employee.stat_empl <> 5;


But still the UPDATE statement won't work because on the where clause,it
use another table, not the EMP_TAXDET table

Any input or suggestions are welcome

TIA,
-regina-