Query in script library to get a list of users getting an error

Question

Hi, 
 
 I'm trying to write a query in a script library to get a number of accounts in ADSAccountInADSGroupTotal that are members of a certain group. Then have a top(nn) clause to limit the number for moving them to another group. 
 
 I am using this: 
 
 Dim colObjects As IColDbObject = Nothing
	Dim currentObject As ISingleDbObject = Nothing
	Dim f As ISqlFormatter = Connection.SqlFormatter	
	Dim uidObject As String
	
	colObjects = Connection.CreateCol("ADSAccountInADSGroupTotal")
	
	colObjects.Prototype.WhereClause = f.Paging("select uid_ADSAccount from ADSAccountInADSGroupTotal Where UID_ADSGroup = '" & Current_UID_ADSGroup & "'","UID_ADSAccount",1,10) 
	
	colObjects.Prototype("UID_ADSAccount").IsDisplayItem = True
	colObjects.Load() 
 (in case the code tag doesn't work: 
 Dim colObjects As IColDbObject = Nothing Dim currentObject As ISingleDbObject = Nothing Dim f As ISqlFormatter = Connection.SqlFormatter Dim uidObject As String colObjects = Connection.CreateCol("ADSAccountInADSGroupTotal") colObjects.Prototype.WhereClause = f.Paging("select uid_ADSAccount from ADSAccountInADSGroupTotal Where UID_ADSGroup = '" & Current_UID_ADSGroup & "'","UID_ADSAccount",1,10) colObjects.Prototype("UID_ADSAccount").IsDisplayItem = True colObjects.Load() 
 And have been getting this error when doing a Test Script: 
 Exception has been thrown by the target of an invocation. Loading list of 'Active Directory user accounts: assignments to groups' objects failed. Error during execution of statement: select UID_ADSGroup, UID_ADSAccount from ADSAccountInADSGroupTotal where (select * from (select *, row_number() over(order by UID_ADSAccount) as XVIRowNumber from (select uid_ADSAccount from ADSAccountInADSGroupTotal Where UID_ADSGroup = '416fdf4a-f3e5-4859-8c29-c48f940b48d9') as x) as y where XVIRowNumber between 1 and 10 order by XVIRowNumber) order by UID_ADSAccount, UID_ADSGroup Database error 1033: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. 
 
 I am using the paging method for the first time and couldn't find any doc for it. 
 This is my attempt to get a batch of uid_ADSAccounts for further processing. 
 If you know other ways to perform the query I am open to suggestions too? 
 Any help is appreciated. 
 Thank You, Todd

Markus Weiss-Ehlers · Accepted Answer

You cannot use the paging in the where clause. But you can do a manual select top 10. 
 Sample code 
 Public Sub CCC_Top10Test(uidGroup As String)
 Dim colObjects As IColDbObject = Nothing
 Dim currentObject As ISingleDbObject = Nothing
 Dim f As ISqlFormatter = Connection.SqlFormatter

 Dim uidObject As String

 colObjects = Connection.CreateCol("ADSAccountInADSGroupTotal")

 colObjects.Prototype.WhereClause = String.Concat("UID_ADSAccount in (Select Top 10 UID_ADSAccount From ADSAccountInADSGroupTotal Where ",
f.Comparison("UID_ADSGroup", uidGroup, ValType.String, CompareOperator.Equal, FormatterOptions.None), ")")

 colObjects.Prototype("UID_ADSAccount").IsDisplayItem = True
 colObjects.Load()

 For Each colElement As IColElem In colObjects
 uidObject = colElement.GetValue("UID_ADSAccount").String
 ' do something for this UID_ADSAccount 
 Next
End Sub

ToddFendt · Answer

Hi Markus, Thank you for the clarifying the paging. It is enlightening. I will need to change the query somehow as the whereclause in clause produces a list of UID_ADSAccount that then drives the main select to produce a record for all groups for that uid_ADSAccount. I am working on that. I also found the documentation on the classes used here in \SDK\Documentation, for anyone interested. They are CHM files. Thanks again Markus for the help!