CA Service Management

Hi Team,

We have created a stored query to display the VIP user raised calls. So as per the new requirement we want to display the tickets raised by VIP users i.e. if the requested_by or affected_resource is a VIP User then it should be displayed in the node. We had made required changes in the scoreboard, however it was throwing the cartesian error message, hence modified the query as below, but it is not working and throwing syntax error when clicking on the scoreboard.

(group.[group]group_list.member IN (@cnt.id))
AND active = 1 AND type = \'R\' AND zfmdpc = 400001 AND status!=\'RE\' AND(
(customer.cnthandling_list.special_handling.sym= \'VIP\' AND requested_by.cnthandling_list.special_handling.sym = \'VIP\') OR
(customer.cnthandling_list.special_handling.sym != \'VIP\' AND requested_by.cnthandling_list.special_handling.sym = \'VIP\') OR
(customer.cnthandling_list.special_handling.sym = \'VIP\' AND requested_by.cnthandling_list.special_handling.sym != \'VIP\'))

Kindly let me know what could be the issue here.

Try this:

(group.[group]group_list.member IN (@cnt.id)) AND
active = 1 AND

type = \'R\' AND

zfmdpc = 400001 AND

status != \'RE\' AND

(
(customer.cnthandling_list.special_handling.sym IN (\'VIP\') AND requested_by.cnthandling_list.special_handling.sym IN (\'VIP\')) OR
(NOT (customer.cnthandling_list.special_handling.sym IN (\'VIP\')) AND requested_by.cnthandling_list.special_handling.sym IN (\'VIP\')) OR
(customer.cnthandling_list.special_handling.sym IN (\'VIP\') AND NOT (requested_by.cnthandling_list.special_handling.sym IN (\'VIP\')))

)

Hi Lindsay,

I tried the above query however the scoreboard part of the page doesn't load, hour glass keeps on spinning. I guess the query is incorrect or is fetching lot of data ..kindly help.

checked the stdlogs and found below errors:

09/28 12:18:19.83 HB_new domsrvr 5432 ERROR wclause.c 1502 Can't find ca_contact_handling in correlation name table to replace cn03_usp_contact_handling; may result in spurious Cartesian join error
09/28 12:18:19.83 HB_new domsrvr 5432 ERROR wclause.c 1502 Can't find ca_contact_handling in correlation name table to replace cn03_usp_contact_handling; may result in spurious Cartesian join error
09/28 12:18:19.83 HB_new domsrvr 5432 ERROR wclause.c 1502 Can't find ca_contact_handling in correlation name table to replace cn03_usp_contact_handling; may result in spurious Cartesian join error
09/28 12:18:19.83 HB_new domsrvr 5432 ERROR wclause.c 1502 Can't find ca_contact_handling in correlation name table to replace cn03_usp_contact_handling; may result in spurious Cartesian join error
09/28 12:18:19.83 HB_new domsrvr 5432 ERROR wclause.c 1502 Can't find ca_contact_handling in correlation name table to replace cn03_usp_contact_handling; may result in spurious Cartesian join error

You're overcomplicating the condition and it might be confusing the query parser/translator. If you take a careful look at your logic you're essentially saying that you want either the customer or the requester to be a VIP. So replace the monstrosity with all the VIP conditions with this:

(customer.cnthandling_list.special_handling.sym = \'VIP\' OR requested_by.cnthandling_list.special_handling.sym = \'VIP\')

It might not solve your problem but it's worth a try and it will make your condition easier to read and understand.

hi Jussi,

The above way is not supported by CA, as it leads to cartesian output error, hence the complexity.

Ah, of course, my bad. I didn't test that on my system before but did so now and it goes as you say. I also tried your original clause, simplified it a bit (dropped all the additional stuff apart from active = 1 and the monster condition) and got the same errors in the stdlog, so it looks like it's nothing you're doing but an issue with the product. You should open a support case for this to have the CA support address this.

Pushpith........

Are you still having issues with this stored query?

Hi Johnny,

I have to given you an idea like, you can add a custom field as integer to "cr" object like "zVIP" when someone insert a ticket, a spelcode function is triggered to set this zVIP=1 by looking customer or requested_by special handling's sym is VIP

So you can write to query as

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

active = 1 AND

type = \'R\' AND

zfmdpc = 400001 AND

status != \'RE\' AND

zVIP = 1

I hope its work