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
.png)
How may I refactor the function or sql to make the random value really random on each of the rows?
Thanks.
Comments
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
Perhaps the built-in function will suit you $TSQL_NEWID()
It will generate a GUID but....
.png)
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, ID, Citizenship, DOB, FirstName, Gender, IDNumber, LastName, PatientNumber, PhoneNumber FROM CDR.Patient
Type of rnd - DOUBLE.
Of course, you can convert it to NUMERIC or STRING
This also do the job, thanks.