I need to use a random () function in SQL in an Informix database version 11.50. However in the select below it returns syntax error:
select random(), codigo from minha_tabela;
674: Routine (random) can not be resolved.
Error in line 1
Near character position 8
Does not the function exist or do you have another way of using it?
The random () function was only natively added to the IBM Informix database at version 11.70.xC6 via " compatibility "package with Oracle (using the Informix datablades feature).
However, you can create your own random function in previous versions of the database. To do so, simply have permission to create SPL / UDRs or ask your DBA to create the code below.
The author of this code is Jonathan Leffler (formerly IBM) and its original version can be found at code repository from IIUG / a> (International Informix User Group).
Example executed through the dbaccess utility:
-- @(#)$Id: random.spl,v 1.2 1997/12/08 19:31:44 johnl Exp $
--
-- Simple emulation of SRAND and RAND in SPL
-- Using random number generator suggested by C standard (ISO 9899:1990)
CREATE PROCEDURE sp_setseed(n INTEGER)
DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;;
LET seed = n;;
END PROCEDURE;
Routine created.
;
CREATE PROCEDURE sp_random() RETURNING INTEGER;;
DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;;
DEFINE d DECIMAL(20,0);;
LET d = (seed * 1103515245) + 12345;;
-- MOD function does not handle 20-digit values... Dammit!!
LET seed = d - 4294967296 * TRUNC(d / 4294967296);;
RETURN MOD(TRUNC(seed / 65536), 32768);;
END PROCEDURE;
Routine created.
;
execute procedure sp_setseed(3414311);
Routine executed.
select first 15 sp_random(), tabid from systables
(expression) tabid
20738 1
14601 2
22109 3
2879 4
24494 5
27611 6
30188 7
30057 8
6287 9
1852 10
18407 11
13089 12
24552 13
30206 14
8225 15
15 row(s) retrieved.
Database closed.
In this release you can already use the DBMS_RANDOM_RANDOM() function.
But for this you need to register the datablade "SQL Packages Extension" (excompat).
* ** Note : Although in version 11.70 xC1 already exists the feature of automatically registering the datablades built-in , it does not register excompat, it manually. But this should only be done once and worth within the connected database. ***
-- Exibindo a versao que esto utilizando
select dbinfo('version','full') from sysmaster:sysdual;
(constant)
IBM Informix Dynamic Server Version 11.70.FC7
1 row(s) retrieved.
-- Registrando o datablade excompat (SQL Extension compatible)
execute function sysbldprepare('excompat.1.0', 'create');
(expression)
0
1 row(s) retrieved.
-- Comparando random SPL com random built-in do Informix ;
select first 15 sp_random(), dbms_random_random() , tabid from systables ;
(expression) (expression) tabid
16838 -1109799718 1
5758 1761650943 2
10113 -2067931720 3
17515 462500326 4
31051 1464472358 5
5627 -2047557286 6
23010 356861852 7
7419 -1157266724 8
16212 1202317650 9
4086 -1302274873 10
2749 186851740 11
12767 1161898564 12
9084 -1151907315 13
12060 755693317 14
32225 -1420713979 15
15 row(s) retrieved.