CA Service Management

Expand all | Collapse all

Stored Query

Jump to Best Answer
  • 1.  Stored Query

    Posted 11-20-2017 04:53 AM

    Hi,

    I need to customize my scoreboard which should check the assigned group name should not be "Pivotts" and all other groups Open incidents.

    But when we create an incident it will not be assigned to any group(which means group will be blank) so its not including in that node. I want to have the new incident also be in the new node.

     

    We used the query "status != \'CL\' AND status != \'RE\' AND type = \'I\' AND (group.last_name = \'Pivotts\' OR group is null)  

     

    Error: 

    AHD05800:Bad where clause. AHD03106:Where clause results in a Cartesian product

     

    Please let me know is there any way to achieve this scenario

     

    Thanks,

    Pramila



  • 2.  Re: Stored Query

    Posted 11-20-2017 08:53 AM

    Hi Pramila,

     

    I think the last part (group.last_name = \'Pivotts\' OR group is null) is probably what is causing the Cartesian product error. What results do you get when you run a similar query directly at database level?

     

    ===

    Kind Regards,

    Brian



  • 3.  Re: Stored Query

    Posted 11-20-2017 10:14 AM

    Hi Pramila.

    Give the following a try:

    "status != \'CL\' AND status != \'RE\' AND type = \'I\' AND (group.last_name in (\'Pivotts\') OR group is null)

    Kind regards

    .........Michael



  • 4.  Re: Stored Query

    Broadcom Employee
    Posted 11-20-2017 10:38 AM

    Michael, this statement works fine. I am curious what "group is null" will do here...when you create a group, you would need to have a name. just wonder...Thanks _Chi



  • 5.  Re: Stored Query

    Posted 11-20-2017 10:42 AM

    Hi Chi.

    It is related to the group attribute of an incident, so it just means Pramila is looking for incidents where the Group is set to  'Pivotts' or where the group is empty.

    Regards

    ...........Michael 



  • 6.  Re: Stored Query

    Posted 11-21-2017 01:06 AM

    Thanks Michael, and sorry. As i mentioned i need to fetch not equal to "pivot" and group is null, 

     

     "status != \'CL\' AND status != \'RE\' AND type = \'I\' AND (group.last_name != \'Pivotts\' OR group is null)  

     

    Can you let me know whether not in works here. I tried  but its throwing the same error.

     

    Thank you so much.

    Pramila



  • 7.  Re: Stored Query

    Posted 11-21-2017 01:08 AM

    It should give result as the incident is not assigned to Pivot also which is not assigned to any group.



  • 8.  Re: Stored Query
    Best Answer

    Posted 11-21-2017 03:43 AM

    I'm sorry. I missed that. The following might work for you:

     "status != \'CL\' AND status != \'RE\' AND type = \'I\' AND (not group.last_name in (\'Pivotts\') OR group is null)"

    regards

    ...........Michael



  • 9.  Re: Stored Query

    Posted 11-27-2017 07:57 AM

    Thank you so much. It works for me.

     

    Thanks,

    Pramila



  • 10.  Re: Stored Query

    Posted 11-27-2017 08:06 AM

    Hi Parmila,

     

    Please mark the response that helped to resolve your issue as the resolution so the items can reflect as answered.

     

    ===

    Kind Regards,

    Brian



  • 11.  Re: Stored Query

    Broadcom Employee
    Posted 11-21-2017 03:20 PM

    Hello,

     

    This tech doc describes the concerns with Cartesian Products:

    What is a Cartesian product in Service Desk and why is it bad? 

     

    Can you try this stored query?
    group.last_name != \'Pivotts\' AND status IN (X, Y, Z...) where X, Y, and Z are status values you are interested in displaying in your stored query.