CA Service Management

Expand all | Collapse all

Modify Query Conditions

Jump to Best Answer
  • 1.  Modify Query Conditions

    Posted 05-21-2017 09:07 AM

    Hi there,

     

    I was recently going through the logs of Service Catalog whilst modifying the policy assignees and found this entry in the log:

     

    2017/05/21 14.26.50.701 INFO [http-bio-80-exec-15] [DBDriver] DB-4 | Query 1-100: select distinct c.inactive, c.contact_uuid, c.userid, c.supervisor_contact_uuid, c.last_name, c.first_name, c.middle_name, c.alias, c.pri_phone_number, c.alt_phone_number, c.fax_number, c.mobile_phone_number, c.pager_number, c.email_address, t.name, l.location_name, l.address_1, l.address_2, l.city, l.county, l.fax_number as fax1, l.pri_phone_number as pri_phone_number1, l.location_uuid, l.zip, s.description as state_name, co.name as country_name, c2.userid as manager, t.name as title_name from ca_contact c left outer join ca_location l on c.location_uuid = l.location_uuid left outer join ca_state_province s on l.state = s.id left outer join ca_country co on l.country = co.id left outer join ca_job_title t on c.job_title = t.id left outer join usm_link_account_user au on c.userid = au.user_id left outer join ca_contact c2 on c2.contact_uuid = c.supervisor_contact_uuid left outer join usm_account a on a.account_id = au.account_id where ( c.last_name collate database_default like ? ESCAPE '\' or c.first_name collate database_default like ? ESCAPE '\' ) and c.inactive = 0 order by c.last_name, c.first_name

     

    Would it be possible to modify the WHERE clause from "where ( c.last_name collate database_default like ? ESCAPE '\' or c.first_name collate database_default like ? ESCAPE '\' ) and c.inactive = 0 order by c.last_name, c.first_name" to "where ( c.alternate_identifier collate database_default like ? ESCAPE '\' or c.userid collate database_default like ? ESCAPE '\' ) and c.inactive = 0 order by c.last_name, c.first_name"

     

    I would also like to include "%"  such as "c.userid collate database_default like ? '%' ESCAPE '%\' "

     

    Please let me know your thoughts and/or advice.

     

    Kind regards,

    Mo



  • 2.  Re: Modify Query Conditions
    Best Answer

    Posted 05-22-2017 07:14 AM

    Good Afternoon Muhammad.
     
    This is in response to your proposed change:
     
    Would it be possible to modify the WHERE clause
    from
    "where ( c.last_name collate database_default like ? ESCAPE '\' or c.first_name collate database_default like ? ESCAPE '\' ) and c.inactive = 0 order by c.last_name, c.first_name"
    to
    "where ( c.alternate_identifier collate database_default like ? ESCAPE '\' or c.userid collate database_default like ? ESCAPE '\' ) and c.inactive = 0 order by c.last_name, c.first_name"

     

    I would also like to include "%" such as "c.userid collate database_default like ? '%' ESCAPE '%\' "
     
    Since this is a 'hard coded' query-text, being used in our internal code, this can not be changes easily as such.
     
    My recommendation then would be to open an Idea for this.
    In general, for situations like this, where a user asks for a change of behaviour of the current release of the product, I can propose to add an Idea in the Communities.
     
    A new way to get your voice heard at CA is to suggest your Idea for review through the Service Management Global User Community.
    The CA Service Catalog Product Team is adopting the CA Community based ideation solution, which is available to you through the CA Service Management Global User Community.  
       
    The following steps will guide you to submit an Idea:
    - Logon to the CA Service Management Global User Community.
    - Select "CA Service Catalog" from the "Products Covered" menu on the right side of the page. (you might need to scroll down a little).
    - Click on the 'Ideas'(bulb-light) icon at the top to view the existing Ideas and see if your Idea has already been suggested.
      When yes, you can then vote-up on it (and add a comment).
    - If you have a new Idea, click on the "Create an Idea" link on the Actions menu at the left.  
     
    Once your Idea is submitted(publish), all members of the Community can view, comment, provide suggestions, discuss alternatives and vote on Ideas to define the highly popular features.
    All Ideas will be reviewed to see whether they can be implemented.
     
    Thanks and kind regards, Louis van Amelsfort.