Jim:

I'm not aware of Informix providing a built-in RANDOM function.  If you're using 9.x there's probably a datablade available for random numbers.

Several years ago, I needed a random number in the worst way, and I needed it fast.  I wrote a stored procedure that called an external Unix Korn shell script which used the internal unix random number.  I inserted the random number into a scratch database table, queried the table from the SP, and returned the number something like this:

select random_num from systables where tablename = "systables".

It worked, but it was relatively slow, and extremely kludgy.

I still have the code somewhere.  I can scrounge it up, if you're really want it.

Regards,

Ed
Schaefer

Many thanks, olded. Specifically I am using Ace reporter with the Informix data and need to generate a "lottery" column. While the function does exist (i.e. man random), the resources for the format apparently do not....yet.

Jim

Jim:

I checked with a couple of Informix/Unix heavy weights I trust.  They said it's impossible with version 7 of the engine to get a random number without some external help.

Regards,

Ed
Schaefer

Jim:

I knew I had seen something on this topic.  Jonathan Leffler, the informix heavy weight of heavy weights, posted this:

--Jonathan Leffler
-- 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;

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...  
    LET seed = d - 4294967296 * TRUNC(d / 4294967296);
    RETURN MOD(TRUNC(seed / 65536), 32768);
END PROCEDURE;

Execute it as such:

select sp_random() from systables where tabname = "systables"

Jonathan hangs out at comp.databases.informix.  If you have a question, I'll bet he'll help.

I grabbed this from the International Informix User's group webpage:

http://www.iiug.org

They have a section on SQL, 4GL, SP, and other tools that's worth checking out.

Regards,


Ed
Schaefer

Again, many thanks to Olded.  I have read Leffler's stuff in that newsgroup in the past. Definitely for advanced folk so I didn't think to post there.

Exactly what I was looking for. Thanks again for your persistence and reply.

Jim