Stored Query

[PramilaJanarthanan]

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

[Legacy User]

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

[Michael Mueller]

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

[Chi Chen]

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

[Michael Mueller]

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 

[PramilaJanarthanan]

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

[PramilaJanarthanan]

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

[Michael Mueller]

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

[PramilaJanarthanan]

Thank you so much. It works for me.

 

Thanks,

Pramila

[Legacy User]

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

[David Ng]

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.