CA Service Management

  • 1.  IN statement for BRELS and brackets

    Posted Oct 05, 2015 06:14 PM

    Hello Community,

     

    As we all know to get tickets assigned to groups of logged in user we should use the following notation:

     

    group.[group]group_list.member IN (@cnt.id)

     

    according to CA documentation (Scoreboard Queries - CA Service Management - 14.1 - CA Technologies Documentation) this will also work:

     

     

    group.group_list.member IN (@cnt.id)

     

    citation from documentation:

    It works in this case, more out of luck, that the group_list object has an attribute named ‘group’ in it. If it were named anything else, the where clause would fail to parse!

     

     

    So i decidet to try another query that will find all incidents that have CI which is related to change 400201:

    affected_resource.[nr]chgnr.chg IN 400201
    
    
    
    
    
    
    
    

     

    According to documentation this query should fail to parse

     

     

    affected_resource.chgnr.chg IN 400201

     

     

    but it also works without any problems and i get the same results as in the case with the brackets.

     

    May it be so, that after CA introduced new lrel system, when each relationship has its own relation table, brackets are absolete? Or i am missing something?

     

     

    There is another statement in documentation:

     

    Note: You cannot extend the dot notation. For example, the following does not work:
    affected_resource.[parent]child_hier.child.name IN ('chicago1')

    It seems that it is not true since following querys works for me as well

     

    (assignee.[member]group_list.group.last_name IN '%UG%')
    (assignee.group_list.group.last_name IN '%UG%')


  • 2.  Re: IN statement for BRELS and brackets

    Posted Oct 14, 2015 02:36 PM

    Does anybody have any information on this?



  • 3.  Re: IN statement for BRELS and brackets

    Posted Jan 20, 2017 09:11 AM

    really interesting.

    no info on my side but curious on any statement back from engineering team

    /J



  • 4.  Re: IN statement for BRELS and brackets

    Posted Jan 21, 2017 03:32 AM

    Hi,

    finally I have found how to create search query using QREL and they willn't work without brackets:

     

    <param>.[<param_value>]<QREL>.<rel_factory_attrs>

     

    for example:

    id.[chg]workflow.status IN ('PEND') - will show all Changes with Pending tasks over workflow QREL declared on chg factory.

     

    But talking about BREL queries with brakets and without, both generate same SQL code.

     

    Regards, cdtj



  • 5.  Re: IN statement for BRELS and brackets

    Posted May 08, 2018 11:09 AM

    I'm trying to do something similar and I hope you can help.  I have created a new lrel table on contacts called zlrel_usp_contact_mgmt. This lrel lists all of the manager's in the contacts org structure. It contains two fields contact_uuid and manager_uuid.  contact_uuid is the SREL to the cnt table that joins the table to the contact record.  manager_uuid is an SREL to cnt for the contact's manager.   

    I added a BREL on cnt pointing to the new lrel table called zmanagement_list.

    I want to perform a search and return all of the contacts in a specific person's management list.

    The SQL would be 

          select * from cnt where id in (select manager_uuid from zlrel_usp_contact_mgmt where contact_uuid = U'xxxxx)

     

    This should equate to 

       id.[manager_uuid].zmanagement_list.contact_uuid = U'D8F6CA6FAE77E94DB8F57DD2CD465D92'

     

    However, I get Attr not found or not atomic when executing the search.  I can easily get a list of all of the contacts that have a particular manager in their list using: 

          zmanagement_list.manager_uuid = U'D8F6CA6FAE77E94DB8F57DD2CD465D92'

     

    However, this is the opposite of what I'm looking for.

     

    Thanks,

    Andrea



  • 6.  Re: IN statement for BRELS and brackets

    Posted May 08, 2018 12:07 PM

    Hi Andrea,

    Sorry but I didn't catch that

    >> I want to perform a search and return all of the contacts in a specific person's management list.

    >> I can easily get a list of all of the contacts that have a particular manager in their list using: 

     

    Feels like:

    select * from cnt where id in (select manager_uuid from zlrel_usp_contact_mgmt where contact_uuid = U'D8F6CA6FAE77E94DB8F57DD2CD465D92')

    match this one:

    zmanagement_list.manager_uuid = U'D8F6CA6FAE77E94DB8F57DD2CD465D92'

     

    Regards,

    cdtj



  • 7.  Re: IN statement for BRELS and brackets

    Posted May 08, 2018 01:26 PM

    Let me see if I can provide an example.  Alexis, David, Diana, and Trish all report under John at some level in the organization tree.  Alexis reports to Trish, John, Matt, and Gordon.  U'D8F6CA6FAE77E94DB8F57DD2CD465D92' is the UUID for John.

          zmanagement_list.manager_uuid = U'D8F6CA6FAE77E94DB8F57DD2CD465D92'  returns Alexis, David, Diana, and Trish. 

    it would represent the SQL: select * from cnt where id in (select contact_uuid from zlrel_usp_contact_mgmt where manager_uuid = U'D8F6CA6FAE77E94DB8F57DD2CD465D92')

     

    I need to return the list of manager's Alexis reports to: Trish, John, Matt, and Gordon. The SQL for that should be:

          select * from cnt where id in (select manager_uuid from zlrel_usp_contact_mgmt where contact_uuid =U'374683AA82ACE34AB999A042F3A0BA2E')

     

    zmanagement_list.contact_uuid = U'374683AA82ACE34AB999A042F3A0BA2E' returns Alexis' record, which I expected.

     

    I've tried :  id.[manager_uuid].zmanagement_list.contact_uuid = U'374683AA82ACE34AB999A042F3A0BA2E' , but I get the error: Atrr not found or not atomic.



  • 8.  Re: IN statement for BRELS and brackets

    Posted May 08, 2018 03:44 PM

    Thanks for clarifying!

     

    Checking other many-to-many relation between same factories like group <-> member would be a clue  (spoiler: they have member_list and group_list BRELs)

    you need to define 2 BRELs:

    zmanagement_list BREL { manager_uuid }
    zcontact_list BREL { contact_uuid }

    then you can use this query:

    zcontact_list.contact_uuid = U'374683AA82ACE34AB999A042F3A0BA2E' 

     

    Regards.



  • 9.  Re: IN statement for BRELS and brackets

    Posted May 08, 2018 04:34 PM

    That did it.  Thank you!