Query syntax help

[Tammy Zadell]

Our web services guy has a portal that shows a list of tickets assigned to groups that I'm a member of.  He wants to add a filter that is based on the analyst's customer location field in their contact record.  In other words, of the tickets that are assigned to groups that I'm a member of, show me tickets that are assigned to analysts that have a customer location of X (what they select in the filter).

 

He has tried the below query syntax, but is getting this error:  "Error fetching: AHD03124:Bad where clause. See log for more details."

 

 

((group = U'524B8E06EE02854D816BDBBF6B6216A1' AND (customer.location.name LIKE 'IDC009%')) OR (group = U'D224F6501447A7429703122DD359938D')) AND active = 1 AND (type = 'I' OR type = 'R') AND parent IS NULL AND status != 'RE'

 

I've told him that I think his query needs to look for the assignee's customer location name, and not just the customer location name, since I think just the customer location name would be the location of the 'affected customer' in the ticket. 

 

Can anyone help with the syntax that would show what he's looking for?

 

Thanks.

Tammy

[Legacy User]

hi,

try to change '=' to 'IN' and use location UUID instead of location name,

If I understood you, this should work:

(
     (group IN U'524B8E06EE02854D816BDBBF6B6216A1' AND customer.location IN (U'<LOCATION_1_UUID>',U'<LOCATION_2_UUID>',U'<LOCATION_N_UUID>')) OR 
     (group IN U'D224F6501447A7429703122DD359938D')
) AND 
active = 1 AND 
(type = 'I' OR type = 'R') AND 
parent IS NULL AND
status != 'RE'

[Anon Anon]

Would you provide a little more information on "the analyst's customer location field in their contact record."?

 

Do you mean the assignee's location based on the assignee's contact record?  If I read your question correctly, it appears you do not want the location of the affected end user (customer.location).

 

J.W.

[Tammy Zadell]

My apologies.  I just got more information from him, and he is trying to use the "affected customer's" location name.  I will pass on the suggestion from cdtj to see if that works -- thanks!

 

Tammy

[Zhijun He]

Hi,

If my memory is right,  multi-dotted attributes might not be supported.

 

Regards,

Mark

[Tammy Zadell]

I know that is true for forms and stored queries, but is it also true for web services queries?  My web services guy said that when he did the query on group and customer location separately (see below), they both worked ok.  Putting them into one query is what is giving him the error.

 

 

((group = U'524B8E06EE02854D816BDBBF6B6216A1') OR (group = U'D224F6501447A7429703122DD359938D')) AND active = 1 AND (type = 'I' OR type = 'R') AND parent IS NULL AND status != 'RE'

 

((customer.location.name LIKE 'IDC009%')) AND active = 1 AND (type = 'I' OR type = 'R') AND parent IS NULL AND status != 'RE'

 

Also --- The above suggestion from cdtj doesn't have multiple dotted attributes, so maybe it will work?  I gave our guy that suggestion to try, but I haven't heard back yet.

 

Thanks,

Tammy            

[Zhijun He]

I believe all the queries will be parsed into real SQL on object layer, no matter the queries are from stored queries, or data partition, or CABI, or web services queries.

As they have the same interpreter, the limitation should be the same.

[Zhijun He]

Exactly, the queries on object layer will be parsed into the queries on schema layer, and then the queries on schema layer will be parsed into SQL that can be run in database.

Some utilities, such as pdm_extract, pdm_load, etc. are working on schema layer, as they don't use the object name, they use the schema name.

[Giedrius Bekintis]

In webservices you can use multiple dot references:

Here is my test request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://www.ca.com/UnicenterServicePlus/ServiceDesk">
   <soapenv:Header/>
   <soapenv:Body>
      <ser:doSelect>
         <sid>597013072</sid>
         <objectType>cr</objectType>
         <whereClause>((customer.location.name LIKE 'test%')) AND active = 1 AND (type = 'I' OR type = 'R') AND parent IS NULL AND status != 'RE'</whereClause>
         <maxRows>1</maxRows>
         <attributes>
            <!--1 or more repetitions:-->
            <string>ref_num</string>
         </attributes>
      </ser:doSelect>
   </soapenv:Body>
</soapenv:Envelope>

 

 

Here is answer from the server

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Body>
      <doSelectResponse xmlns="http://www.ca.com/UnicenterServicePlus/ServiceDesk">
         <doSelectReturn xmlns=""><![CDATA[<?xml version="1.0" encoding="UTF-8"?><UDSObjectList>
<UDSObject>
<Handle>cr:400352</Handle>
<Attributes>
<Attribute DataType="2002">
<AttrName>ref_num</AttrName>
<AttrValue>65</AttrValue>
</Attribute>
</Attributes>
</UDSObject>
</UDSObjectList>]]></doSelectReturn>
      </doSelectResponse>
   </soapenv:Body>
</soapenv:Envelope>

[Giedrius Bekintis]

In tzadell case the full error was Error fetching: AHD03053:Bad where clause: AHD03106:Where clause results in a Cartesian product

So he needs to rewrite query to:

(group = U'524B8E06EE02854D816BDBBF6B6216A1' AND customer.location.name LIKE 'IDC009%') OR (group = U'D224F6501447A7429703122DD359938D' AND customer.location.name NOT LIKE 'IDC009%') OR (group = U'D224F6501447A7429703122DD359938D' AND customer.location.name LIKE 'IDC009%') AND active = 1 AND (type = 'I' OR type = 'R') AND parent IS NULL AND status != 'RE'

 

And everything should be fine.

[Tammy Zadell]

Hi Gutis,

I'm a little confused about the part in your query shown in red below:

 

(group = U'524B8E06EE02854D816BDBBF6B6216A1' AND customer.location.name LIKE 'IDC009%') OR (group = U'D224F6501447A7429703122DD359938D' AND customer.location.name NOT LIKE 'IDC009%') OR (group = U'D224F6501447A7429703122DD359938D' AND customer.location.name LIKE 'IDC009%') AND active = 1 AND (type = 'I' OR type = 'R') AND parent IS NULL AND status != 'RE'

 

If IDC009 was the location selected in the filter that he's providing, why would the query check for customer.location.name LIKE 'IDC009%' --- OR --- customer.location.name NOT LIKE 'IED009%' ?

 

Thanks.

Tammy

[Giedrius Bekintis]

According to your initial query you need to list incidents that are assigned to Group U'524B8E06EE02854D816BDBBF6B6216A1' and Customer location is 'IDC009%', OR incidents that are assigned to group U'D224F6501447A7429703122DD359938D'

 

Incidents that are assigned to group U'524B8E06EE02854D816BDBBF6B6216A1' and customer location is from  'IDC009%' is covered by this part of query

(group = U'524B8E06EE02854D816BDBBF6B6216A1' AND customer.location.name LIKE 'IDC009%')

 

Incident that are assigned to group U'D224F6501447A7429703122DD359938D' is covered by second part of query:

(group = U'D224F6501447A7429703122DD359938D' AND customer.location.name NOT LIKE 'IDC009%') OR (group = U'D224F6501447A7429703122DD359938D' AND customer.location.name LIKE 'IDC009%')

so in this part we will list all incidents that are assigned to group U'D224F6501447A7429703122DD359938D' and customer is not from IDC009%' OR all incidents that are assigned to U'D224F6501447A7429703122DD359938D' and customer is from IDC009%', so basically all incidents that are assigned to U'D224F6501447A7429703122DD359938D'. It is a little trick to avoid Cartesian product error

[Tammy Zadell]

Hi Gutis,

 

I think what he's trying to accomplish is this:   Provide a filter to select one or more customer location names, so that it will only show tickets that are assigned to the routing groups I'm a member of (could be one or more), where the customer location name is what is selected in the filter (one or more of them). 

 

Tammy

[Giedrius Bekintis]

Than your query should look like this:

 

updated:

((group = U'524B8E06EE02854D816BDBBF6B6216A1' AND customer.location.name LIKE 'IDC009%') OR (group = U'D224F6501447A7429703122DD359938D' AND customer.location.name LIKE 'IDC009%')) AND active = 1 AND (type = 'I' OR type = 'R') AND parent IS NULL AND status != 'RE'

[Giedrius Bekintis]

Your query result in non Cartesian product, that is what your full error should say. Try to change

((group = U'524B8E06EE02854D816BDBBF6B6216A1' AND customer.location.name LIKE 'IDC009%') OR (group = U'D224F6501447A7429703122DD359938D'))

 

to

 

(group = U'524B8E06EE02854D816BDBBF6B6216A1' AND customer.location.name LIKE 'IDC009%') OR (group = U'D224F6501447A7429703122DD359938D' AND customer.location.name NOT LIKE 'IDC009%') OR (group = U'D224F6501447A7429703122DD359938D' AND customer.location.name LIKE 'IDC009%')

 

So your full query should look like this:

(group = U'524B8E06EE02854D816BDBBF6B6216A1' AND customer.location.name LIKE 'IDC009%') OR (group = U'D224F6501447A7429703122DD359938D' AND customer.location.name NOT LIKE 'IDC009%') OR (group = U'D224F6501447A7429703122DD359938D' AND customer.location.name LIKE 'IDC009%') AND active = 1 AND (type = 'I' OR type = 'R') AND parent IS NULL AND status != 'RE'

[Tammy Zadell]

Hi everyone,

Our web services guy has decided to accomplish this in another way.  But thanks everyone for the help!

 

Tammy