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:
<FONT COLOR="#0000ff">SELECT </FONT><FONT COLOR="#008000">%TSQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">ZRAND</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">%ID</FONT><FONT COLOR="#000000">+1e16) </FONT><FONT COLOR="#008000">rnd</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">ID</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">Citizenship</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">DOB</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">FirstName</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">Gender</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">IDNumber</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">LastName</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">PatientNumber</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">PhoneNumber </FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">CDR</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Patient</FONT>Type of rnd - DOUBLE. Of course, you can convert it to NUMERIC or STRING
This also do the job, thanks.