Nicky Zhu · Nov 3, 2021

How to add a column of random numbers in a sql result?

Hi guys,


My client has a requirement to add a column of random numbers to the query result.

I wrote a function as below:

Class Utils.SqlUtility Extends %RegisteredObject

ClassMethod GetSomeNumber(intInput As %Integer) As %Integer [ SqlName = GetNumber, SqlProc ]
    Return $R(intInput)


But in the returned sql result, every row share the same value, as below,

SELECT Utils.GetNumber('456'),
ID, Citizenship, DOB, FirstName, Gender, IDNumber, LastName, PatientNumber, PhoneNumber
FROM CDR.Patient



How may I refactor the function or sql to make the random value really random on each of the rows?


0 563
Discussion (6)1
Log in or sign up to continue


is a static expression and not depending on rows so it is calculated only once.

do this changes:

ClassMethod GetSomeNumber(intInput As %Integer, id ) As %Integer [ SqlName = GetNumber, SqlProc ]

and your query

SELECT Utils.GetNumber('456',ID), ....

by adding ID you force a recalculation by a row dependency

It works, thank you very much Robert

It will generate a GUID but....

We may need to wrap it into a function and force the call with row dependency as Robert mentioned above.


In this case, you can do this:

FROM CDR.Patient

Type of rnd - DOUBLE.
Of course, you can convert it to NUMERIC or STRING

This also do the job, thanks.