Hi all,
i have next problem: my UDF function returns same vale on each new row.
what is wrong? i need different value for each row.
can you help me pls?

see code :
create table t11 (col1 bigint );
insert into t11 values (1) ;
insert into t11 values (2) ;
insert into t11 values (3) ;
commit;
create function rand_x (i_min int, i_max int )
returns float
language sql
not deterministic
begin atomic
return value(i_min, 0) +(value(i_max, 1)- value(i_min, 0))*rand ();
end ;

select rand_x(1, 100) as bad_col , rand() as ok_col from t11;

BAD_COL OK_COL
61.586779381695 0.0278328806421094
61.586779381695 0.329844050416578
61.586779381695 0.0559099093600269

thans much advance

looks strange.

Output from the function should be FLOAT, but BAD_COL seems to be DECIMAL .

I created the function on our testsystem ( DB2 UDB for z/OS V8 NFM )
omitted the textstring "begin atomic" and the function worked fine.


CREATE FUNCTION RAND_X (I_MIN INT, I_MAX INT )
RETURNS FLOAT
LANGUAGE SQL
NOT DETERMINISTIC

RETURN VALUE(I_MIN, 0) +(VALUE(I_MAX, 1)- VALUE(I_MIN, 0))*RAND ();




SELECT RAND_X ( 1,100), RAND()
FROM SYSIBM.SYSTABLES
FETCH FIRST 10 ROWS ONLY ;

gave as a result:

COL1 COL2
---------- ----------
5.417E+01 7.872E-02
2.944E+01 7.680E-01
6.084E+01 4.268E-02
4.956E+01 8.509E-01
4.169E+01 7.381E-01
6.663E+01 5.565E-01
9.740E+00 4.582E-01
7.174E+01 6.858E-01
6.837E+01 6.786E-02
6.867E+01 2.851E-01

umayer: works, thanks very much

next level. when UDF must contains more than one command, is necessary begin atomic/ end statement. this causes repeating value on UDF result. some ideas ?
RTFM? bug? feature?
db version is 8.2.3. LUW

Initially, this looks like a defect in DB2. You may want to try it with the latest FixPak to see if this is already fixed. If not, open a PMR with IBM support.

I recommend that you try to change the original function to EXTERNAL ACTION (default is NO EXTERNAL ACTION). With this, DB2 must make sure that the function body is called as often as is mandated by the SQL statement you typed in (and not what the optimizer may make of it).

Furthermore, your claim that one needs compound statements can usually not be held up. I found so far that it is generally possibly to write procedural SQL statements into relational SQL. (Alas, thinking in sets is not so well practiced amongst the many people who work with relational DBMS.)

stolze:
thanks
sorry for delay.