Hi Tobias,

Tobias Giesen wrote:

>
> > Only UDF's are allowed in UPDATE statements.
>
> Ok, thanks. If I have any more problems, I'll go back to the Interbase
> support list.

although Thomas is basically right, there *is* a way to use storedprocs in
updates. i just played around a bit, this is what i found:

first of all, you could use the Truncate udf in FreeUDFLib.DLL:

update betraege set betrag = f_truncate(betrag) where blah...

well, this made me a bit curious (there must be a way without using udfs),
so i just tested the following (FB 0.94 / SQL dialect 3):

CREATE TABLE BETRAEGE (
BETRAG DOUBLE PRECISION,
B2 DOUBLE PRECISION);

INSERT INTO BETRAEGE (BETRAG) VALUES (0);
INSERT INTO BETRAEGE (BETRAG) VALUES (1);
INSERT INTO BETRAEGE (BETRAG) VALUES (0.1);
INSERT INTO BETRAEGE (BETRAG) VALUES (0.5);
INSERT INTO BETRAEGE (BETRAG) VALUES (0.9);
INSERT INTO BETRAEGE (BETRAG) VALUES (-1);
INSERT INTO BETRAEGE (BETRAG) VALUES (-0.1);
INSERT INTO BETRAEGE (BETRAG) VALUES (-0.9);

update betraege set b2 = cast( (betrag-0.499999) as integer)

seems to do the job:

BETRAG B2
0 0
1 1
0,1 0
0,5 0
0,9 0
-1 -1
-0,1 -1
-0,9 -1

Note i used -0.499999, because -0.5 would give you -1 for 0.00000
Since you seem to be calculating currencies, this should not be a problem -
however, not very satisfiying. so next attempt: use a storedproc

create procedure sp_Floor(inval double precision)
returns (outval double precision)
as
begin
if (inval = cast(inval as integer)) /* exactly .000000 */
then outval = inval;
else outval = cast(inval - 0.5 as integer); /* make that + 0.5 for sp_Ceil */
suspend;
end

and then

update betraege set b2 = (select outval from sp_Floor(betraege.betrag))

which *does* work, and gives exactly the same result as before - remember
Thomas' reply (syntax of update statments):
[snip]
<expr> = A valid SQL expression that results in a single value.
[/snip]

the sub-select in the above update statement is just that, so you *can*
use sp's in updates *in this special case*.

So here we are "On Topic" again: with the sp_Floor sproc, you can use a
TIB_DSQL component to execute the Update statement... ;-)

hope this helps (it did help *me* to write this!<g>),
fingerman