Hello all,

I hope i've found the source of occasional error "Int overflow or string
truncation. Can not convert from string 308" (see my prev posts). There
are some tables:

create table prods (
id integer not null primary key,
...
price numeric(18,4),
rate float not null,
check (rate > 0)
);

create table prod_set (
parent integer not null references prods(id),
child integer not null references prods(id),
qty numeric(18,4),
primary key (parent, child)
);

create table ord_rows (
id integer not null primary key,
prodid integer not null references prods(id),
..
price numeric(18,4),
price qty(18,4)
);

And this is a new SP, what produce that execution-time error:

create procedure add_set (proidid integer, rate float) as
declare variable p numeric(18,4);
declare variable q numeric(18,4);
begin
insert into ord_rows (id, prodid, price, qty)
select gen_id(g_ord_row, 1), s.child, :rate*s.price/p.rate, s.qty
from prod_set s
inner join prods p on s.child = p.id and s.child = :prodid;
end

I've modified it:

create procedure add_set (proidid integer, rate float) as
declare variable p numeric(18,4);
declare variable q numeric(18,4);
declare variable pid integer;
declare variable i integer;
begin
for select s.child, s.price/p.rate, s.qty from prod_set s
inner join prods p on s.child = p.id and s.child = :prodid into
:pid, :p, :q
do begin
i = gen_id(g_ord_row, 1);
insert into ord_rows (id, prodid, price, qty) values (:i, :pid,
:rate*:p, :q);
end
end

The same error at "for select " statement!

And final working version is:

create procedure add_set (proidid integer, rate float) as
declare variable p numeric(18,4);
declare variable q numeric(18,4);
declare variable pid integer;
declare variable i integer;
declare variable x float;
begin
for select s.child, s.price/p.rate, s.qty from prod_set s
inner join prods p on s.child = p.id and s.child = :prodid into
:pid, :x, :q
do begin
i = gen_id(g_ord_row, 1); p = x*rate;
insert into ord_rows (id, prodid, price, qty) values (:i, :pid,
:p, :q);
end
end

Passed "rate" was 5.5. prods.price contains values 3..350, prods.rate 1..6

Any comments ?