CODEWITHSUNDEEP
sqlinformix
user3525444
user3525444

Reputation: 65

How to update field with the random integer value in the informix db

I need to do something like this: 

UPDATE sometable SET somefield = randomint(1-19);

It is possible to do in informix? Function dbms_random_random doesn`t work.

enter image description here

Upvotes: 1

Views: 1854

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 754480

If loading the provided but not pre-installed extension identified by Hamza Abdaoui in the answer is too onerous (or requires more privileges than you have on the database), then consider using this code:

-- @(#)$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;

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;

It generates numbers over the range of 16-bit positive integers. It is based on the the 'minimally acceptable' random number generator mentioned in the C standard (it is now ISO/IEC 9899:2011, of course, but the code is the same; it's been a while since I last needed to modify this source code).

If this is not acceptable, then get the SQL Extension Package installed. If you need more guidance than the pointers under Hamza's answer, leave a comment.

Upvotes: 3

Hamza Abdaoui
Hamza Abdaoui

Reputation: 2209

From the Documentation, you can use dbms_random_random() LIKE THIS : 

UPDATE sometable SET somefield = dbms_random_random();

dbms_random_random is a routine that comes with the DBMS_RANDOM package that can be installed following this tutorial : How to set up and use DBMS_RANDOM.

Upvotes: 2

Kareem Essawy
Kareem Essawy

Reputation: 615

Random Decimal Range

To create a random decimal number between two values (range), you can use the following formula:

SELECT RAND()*(b-a)+a;

Where a is the smallest number and b is the largest number that you want to generate a random number for.

SELECT RAND()*(25-10)+10;

The formula above would generate a random decimal number >= 10 and < 25. (Note: this formula will never return a value of 25 because the random function will never return 1.)

Random Integer Range

To create a random integer number between two values (inclusive range), you can use the following formula:

SELECT FLOOR(RAND()*(b-a+1))+a;

Where a is the smallest number and b is the largest number that you want to generate a random number for.

SELECT FLOOR(RAND()*(25-10+1))+10;

The formula above would generate a random integer number between 10 and 25, inclusive.

Check the Source

Upvotes: -1

Related Questions