format for using Random function

[mars19301]

Trying to create an Ace report with a column or just updating a field to assign randomly assign room numbers to students (alpha order). So I am looking for the syntax/format for random() and srandom()

I know this is a rare bird, but thought I'd ask. Been surprised before at the available knowledge inside this monitor. Thank you in advance.

Jim

 

[olded]

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

 

[mars19301]

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

 

[olded]

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

 

[olded]

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

 

[mars19301]

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