Question
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?

Thanks.

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

Utils.GetNumber('456')

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.

Thanks.

In this case, you can do this:

SELECT %TSQL.ZRAND(%ID+1e16) rnd,
IDCitizenshipDOBFirstNameGenderIDNumberLastNamePatientNumberPhoneNumber
FROM CDR.Patient

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

This also do the job, thanks.