Query in a scrip with order by condition

Hi everyone,

I'm creating a custom scrip in which I need to execute a query with the order by clause. I specified it in the where caluse but there is a default "order by cn" in the final query. Here you can see the two queries.

Where clasue: "UID_UNSGroupB in (select top 27 UID_UNSGroupB from UNSGroupB where UID_UNSContainerB = 'c4f3feb6-9dde-4768-9545-1d0f7c313321' order by ObjectGUID)"

Query executed in the database with my where clause: "select UID_UNSGroupB, cn, CanonicalName, xmarkedfordeletion from UNSGroupB where ((UID_UNSGroupB in (select top 27 UID_UNSGroupB from UNSGroupB where UID_UNSContainerB = 'c4f3feb6-9dde-4768-9545-1d0f7c313321' order by ObjectGUID))) order by cn"

Is there a way to avoid the "order by cn" at the end?

Thank you

Simone

Parents
  • I assume you used the where clause to load a collection. The default load-type for the collection always sorts by the display pattern of the objects loaded if no order-by was specified in the query object.

    To avoid this, you can either specify an order-by UID_UNSGroupB (if that solves your issue) or use the load-type Slim.

    Be aware that this load-type does not load the display value for the objects or column values.

Reply
  • I assume you used the where clause to load a collection. The default load-type for the collection always sorts by the display pattern of the objects loaded if no order-by was specified in the query object.

    To avoid this, you can either specify an order-by UID_UNSGroupB (if that solves your issue) or use the load-type Slim.

    Be aware that this load-type does not load the display value for the objects or column values.

Children
  • Hi Markus,

    Thanks for your reply. I'm new with One Identity and i don't know the difference between the two.

    Right now i'm usnig this code to execute the query:

    Dim f As ISqlFormatter = Connection.SqlFormatter              
    Dim colTotRole As IColDbObject = Connection.CreateCol("UNSGroupB")              
    colTotRole.Prototype.WhereClause = "UID_UNSGroupB in (select top 27 UID_UNSGroupB from UNSGroupB where UID_UNSContainerB = 'c4f3feb6-9dde-4768-9545-1d0f7c313321' order by ObjectGUID)"
    colTotRole.Load()  

    And then i hace a for cycle (with "i" as index of the cycle) to get each row in this way:

    Dim Receiver As ISingleDbObject = colTotRole(i).Create
    Dim RoleUID As String = Receiver.ObjectWalker.GetValue("UID_UNSGroupB").String

    In which way shoud i change my code to be able to order by ObjectGUID? 

  • First of all, I would recommend the video series about scripting in One Identity Manager on our official YouTube channel. It explains all you need to know.

    https://www.youtube.com/playlist?list=PL242czeZwlAk0T2AcqpSFtBOXQfkP9f5J

    Secondly, I suggest to use the new API (new since version 7) to work with the objects. This API is explained in the scripting SDK Samples on the product delivery <OneIM>\Modules\QBM\dvd\AddOn\SDK\ScriptSamples

    If you do use the new API, your code would look like this:

    Dim whereClause As String = "UID_UNSGroupB in (select top 27 UID_UNSGroupB from UNSGroupB where UID_UNSContainerB = 'c4f3feb6-9dde-4768-9545-1d0f7c313321' order by ObjectGUID)"
    Dim dbQuery = Query.From(Table.UNSGroupB).Where(whereClause).SelectNonLobs()
    ' Load the collection without display and sorting
    Dim dbTotRole As IEntityCollection = Session.Source.GetCollection(dbQuery, EntityCollectionLoadType.Slim)
    
    ' Iterate over the collection
    For Each elem As IEntity In dbTotRole
        ' Fetch the GUID of the Group form the collection
        Dim RoleUID As String = elem.GetValue(Table.UNSGroupB.UID_UNSGroupB).String
        ' Do something with the RoleUID
    Next

  • Thanks for the tip, unfortunatly trying your code i recive this error:

    Local variable 'Query' cannot be referred to before it is declared.

    I checked the video you suggest me and alse there they use "Query" in the same way so i'm confused, why is it not working? I'm using the 8.1.5 version on One Identity, i don't know if it is a useful information. 

  • My code works fine in a sub or function in a dialog script. I assumed your code was in a method stored in DialogScript.

    Maybe something else is wrong with your code (the parts that you didn't share).