CA Service Management

 View Only
Expand all | Collapse all

Query syntax help

Jump to Best Answer
  • 1.  Query syntax help

    Posted Apr 22, 2015 07:00 AM

    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




  • 2.  Re: Query syntax help

    Posted Apr 22, 2015 08:16 AM

    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'
    


  • 3.  Re: Query syntax help

    Posted Apr 22, 2015 09:27 AM

    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.



  • 4.  Re: Query syntax help

    Posted Apr 22, 2015 09:56 AM

    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



  • 5.  Re: Query syntax help

    Broadcom Employee
    Posted Apr 22, 2015 09:53 PM

    Hi,

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

     

    Regards,

    Mark



  • 6.  Re: Query syntax help

    Posted Apr 23, 2015 06:06 AM

    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            



  • 7.  Re: Query syntax help

    Broadcom Employee
    Posted Apr 23, 2015 07:40 PM

    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.



  • 8.  Re: Query syntax help

    Broadcom Employee
    Posted Apr 23, 2015 08:16 PM

    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.



  • 9.  Re: Query syntax help

    Posted Apr 24, 2015 02:37 AM

    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>
    


  • 10.  Re: Query syntax help

    Posted Apr 24, 2015 02:42 AM

    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.



  • 11.  Re: Query syntax help

    Posted Apr 24, 2015 06:07 AM

    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



  • 12.  Re: Query syntax help

    Posted Apr 24, 2015 07:10 AM

    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



  • 13.  Re: Query syntax help

    Posted Apr 24, 2015 07:24 AM

    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



  • 14.  Re: Query syntax help

    Posted Apr 24, 2015 07:28 AM

    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'



  • 15.  Re: Query syntax help

    Posted Apr 23, 2015 12:55 PM

    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'



  • 16.  Re: Query syntax help
    Best Answer

    Posted Apr 24, 2015 03:44 PM

    Hi everyone,


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

     

    Tammy